I want to be able to calculate by month how many submissions should have been done up to today taking into consideration the start and end of the subcontractor. So for example, for project 2585 & subcontractor 2513 it should show 1 submission due for each month from May-20 till Mar-21. For subcontractor 2306 it should show 1 submission due from May-20 till Dec-20. I can calculate the number of submissions but I don’t know how to associate it to show per month. Can anyone help? My date table is at daily level
If you have already have a measure, your model set up correctly and that includes a proper Date table basically all you have to do is add a date attribute to your visual something like a “Month & Year” field for example.
If you need more assistance please provide a sample PBIX and elaborate on how you need that visualized.
Hi Melissa
Thanks for your reply. I have a Month & year field on my date table but I am struggling to generate a row per month that the report has not been submitted. So for example on the first line I have used DATEDIFF to calculate that 10 months from May-20 to Mar-21 have not been submitted but this sits as one row and not one row per month. Unfortunately I can’t upload the .pbix as it is sensitive data
Hi Melissa - thanks for the video. I have masked the data and uploaded. You can see the chart I am trying to achieve but the data is not correctly split across the months as it all shows against the first month that is overdue
First I made a change to your Model. This is now an inactive relationship which means you have to activate it with USERELATIONSHIP inside CALCULATE for each measure that requires it.
Hi @elaine-connelly. Welcome to the Forum! It’s great to know that you are having a great experience so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
not to worry here’s a video on masking your data.
