Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
JamesBockett
Helper I
Helper I

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 IdMilestone 1 Due DateMilestone 1 Revised Due Date

Milestone 1 Completed Date

Milestone 2 Due DateMilestone 2 Revised Due Date

Milestone 2 Completed Date

Milestone 3 Due DateMilestone 3 Revised Due Date

Milestone 3 Completed Date

ABC12310/02/202215/02/2022

16/02/2022

12/03/202217/03/202216/03/202220/03/202221/03/202221/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 7
SpartaBI
Community Champion
Community Champion

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.


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Showcase Report – Contoso By SpartaBI

Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

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.


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Showcase Report – Contoso By SpartaBI

Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Thanks again @SpartaBI - I don't have a sample dataset at the moment but the unpivoted table now looks like this:

 

Case IDMilestoneDate
ABC123Milestone 1 Due Date30/05/2022
ABC123Milestone 1 Revised Due Date31/05/2022
ABC123Milestone 1 Completed Date31/05/2022
ABC123Milestone 2 Due Date31/05/2022
ABC123Milestone 2 Revised Due Date01/06/2022
ABC123Milestone 2 Completed Date01/06/2022
ABC123Milestone 3 Due Date05/06/2022
ABC123Milestone 3 Revised Due Date05/06/2022
ABC123Milestone 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

@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.



2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Showcase Report – Contoso By SpartaBI

Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Hi @SpartaBI - Sadly, this didn't work and my table goes blank when I add the measure.

JamesBockett_0-1663000135274.png

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

JamesBockett_1-1663000312472.png

 

Each case ID could have upto 63 milestone dates.

 

Many thanks

James

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.