Date Differences

[JamesBockett]

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

[SpartaBI]

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.

Showcase Report – Contoso By SpartaBI



      

[JamesBockett]

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

[SpartaBI]

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.

Showcase Report – Contoso By SpartaBI



      

[JamesBockett]

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

[SpartaBI]

@JamesBockett check this sample solution I did for one measure:
Date Differences 2022-08-09.pbix

Let me know if that is what you meant.

Showcase Report – Contoso By SpartaBI



      

[JamesBockett]

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

[SpartaBI]

@JamesBockett  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 🙂