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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Water
Helper I
Helper I

DAX for difference between each date and previous date per occurrence

Hi, 

 

Please help with DAX to accomplish the following?

 

For  each vehicle many jobs are performed at various date intervals.  Each time a job is performed, the date (JOB_OPEN_DATE) is captured. Below in the table you can see the JOB_OPEN_DATEs when different jobs in the JOB column were performed at various dates for one selected vehicle. (There are of course many more vehicles.)

 

Question: I don't want to display the actual date when each job was performed as you can see in the below table, but rather the difference between each date and the previous date for each job - in number of days.   This is to see clearly the interval (in days) at which the jobs were performed. This means e.g. for the first Job 06-02-005, 2021 June should display (2021-06-23 minus 2020-06-17) = 371 (days). In the same way, 2022 July should display (2022-07-05 minus 2021-06-23) = 377 (days).

 

Date.jpg

The ideal solution should like like the below showing number of days: 

Solution I am hoping for days difference.jpg

All the data is captured in the same WO WITH JOB DETAIL table. Here sample data from this table: Sample dataset . The data captured in Power BI is here. Not sure if I should add a Date/Calendar table. This Power Bi file  contains a date table.

 

You will note the interval between the various jobs differs.

 

One would typically filter on a specific vehicle to monitor service intervals for its many jobs.

 

With thanks, 

 

Water

2 REPLIES 2
lbendlin
Super User
Super User

This is not giving you what you asked for, but probably what you wanted.

 

lbendlin_0-1696631718526.png

 

Thank you very much for an inbetween solution! 

 

However, idealy I still hope for an output like the below in days difference:

 

Solution I am hoping for days difference.jpg

 

Updated the Power BI file with a better date relatonships now that I understand that better. 

 

Would you mind to please have another look?

 

With hope and thanks, 

 

Water 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors