Forum Discussion
Date Differences
Hi
I have a table of 64 columns. The first column is the unique ID (known internally as 'Case ID'), the other 63 columns are milestone dates. There are 21 milstones applicable to each case and each milestone has 3 dates:
1. Due date (the date, calculated by the database, that the milestone is due)
2. Revised due date (the date, manually adjusted, to take into account leave/resource challenges/other priorities)
3. Completed date (the date the milestone was completed).
An example of the first 10 columns is below:
| Case Id | Milestone 1 Due Date | Milestone 1 Revised Due Date | Milestone 1 Completed Date | Milestone 2 Due Date | Milestone 2 Revised Due Date | Milestone 2 Completed Date | Milestone 3 Due Date | Milestone 3 Revised Due Date | Milestone 3 Completed Date |
| ABC123 | 10/02/2022 | 15/02/2022 | 16/02/2022 | 12/03/2022 | 17/03/2022 | 16/03/2022 | 20/03/2022 | 21/03/2022 | 21/03/2022 |
I need to show the days between each date, for each milestone. i.e.
Milestone 1:
- Days between due date and completed date,
- Days between due date and revised due date,
- Days between revised due date and completed date,
and so on for milestone 2, 3, 4 etc...
I know I can use the DATEDIFF but this means creating 3 additional calculated columns for every milestone, meaning my table will end up being 126 columns wide. Is there a better/easier way to do this like using a measure or am I stuck with creating calculated columns for each?
Many thanks in advance,
James
7 Replies
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.
