Forum Discussion
Date Differences
Well, as oppose to a calculated column that is based on the current row, a measure result is dependant on how you will use it in the visual.
It can be as simple as:
Days between due date and completed date: MAX(Milestone 1 Completed Date) - MAX(Milestone 1 Due Date) if you will put it in a matrix and have a row for each case id.
In case you will unpivot like I suggested you will probably need to do much less measures. They will have a little bit more logic but nothing special. If you share a pbix of sample data I could show you or you could give it a go first on your own.
Thanks again SpartaBI - I don't have a sample dataset at the moment but the unpivoted table now looks like this:
| Case ID | Milestone | Date |
| ABC123 | Milestone 1 Due Date | 30/05/2022 |
| ABC123 | Milestone 1 Revised Due Date | 31/05/2022 |
| ABC123 | Milestone 1 Completed Date | 31/05/2022 |
| ABC123 | Milestone 2 Due Date | 31/05/2022 |
| ABC123 | Milestone 2 Revised Due Date | 01/06/2022 |
| ABC123 | Milestone 2 Completed Date | 01/06/2022 |
| ABC123 | Milestone 3 Due Date | 05/06/2022 |
| ABC123 | Milestone 3 Revised Due Date | 05/06/2022 |
| ABC123 | Milestone 3 Completed Date | 07/06/2022 |
and so on for all 12 milestones...
I've tried some different measures but my Powier BI / DAX writing skills are not very good yet. What DAX would you use?
James
Anonymous that's a good table. Will send you an example on that soon.
Please don't forget to accept the relevant messages as solutions and appreciate your kudos 🙂Anonymous check this sample solution I did for one measure:
Date Differences 2022-08-09.pbix
Let me know if that is what you meant.Hi SpartaBI - Sadly, this didn't work and my table goes blank when I add the measure.
I have a lot of different case ID's, it is not just ABC123 it also contains other case IDs, for example, DEF456, GHI789, JKL101112.
Here is a screenshot of the table in its 'unpivotted' format
Each case ID could have upto 63 milestone dates.
Many thanks
James