Forum Discussion
Date Differences
better to create measures anyway instead of calculated columns for this case. Your table has already too much columns.
I would actually suggest to unpivot as much as posibble:
case id | milestone | date type | date
so only 4 columns. Much better for model compression.
Thanks SpartaBI, I was thinking of measures. Can you give me an example of how the measure should be written? I've had a go but want to get experts opinions
Cheers
James
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