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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.