Show in table difference between previous and following value/date for each occurrence

[Water]

Dear Experts, 

Please help me with the correct DAX for the following:

At Fleet we have many units (vehicles). Each unit has many jobs performed on it at different time or mileage intervals. Each time a job is performed, the date and mileage (Meter KM) are captured. Below are two tables displaying the date and mileage when the different Jobs in the JOB column were performed for one selected vehicle. (There are of course many more vehicles.)

Question: I would like to display in the tables the difference between each previous and its following value or date per each job  - and not the actual value. This is to see clearly the interval in which the jobs get performed. This means e.g. with in the mileage table for the first Job 06-02-005, 2021 June should display (147,584-136,011)= 11,573, and for 2022 July (162,981-147,584=) 15,397.

For the date table I hope to display, with the same logic, the number of days difference between each previous and its following date per each job. This means e.g. for the first Job 06-02-005 in the date table, 2021 June should display (2021-06-23 minus 2020-06-17) = 371 (days).

All the data is captured in the same WO WITH JOB DETAIL table. Here sample data from this table: Sample dataset  (Deleted unnecessary fields.) The data captured in Power BI is  here. 

You will see jobs are performed over various intervals, ranging from once every 3rd or 4th year, to once every two weeks to everything in between. 

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

I specifically don't include a screenshot of my table relationships as, since all the data fields are in the same table, I believe that is not necessary(?).

Thank you in anticipation, 

Water 

ps. This is an improved repost of a post initially under the wrong Forum. I cannot find a way to delete or close the previous post: 

Re: Display difference between previous and follow... - Microsoft Fabric Community

[123abc]

To achieve the desired result in Power BI using DAX, you can create two measures: one for calculating the difference in mileage and another for calculating the difference in dates for each job. You can then create tables or visuals to display these measures.

Here's how you can create these measures:

1. Difference in Mileage:

   Create a new measure to calculate the difference in mileage for each job. Assuming you have a table named Jobs with columns Job, Vehicle, JobDate, and MeterKM, you can use the following DAX measure:

Mileage Difference =
VAR CurrentJobMeterKM = MAX(Jobs[MeterKM])
VAR PreviousJobMeterKM =
CALCULATE(
MAX(Jobs[MeterKM]),
FILTER(
Jobs,
Jobs[Vehicle] = MAX(Jobs[Vehicle]) &&
Jobs[Job] = EARLIER(Jobs[Job]) &&
Jobs[JobDate] < EARLIER(Jobs[JobDate])
)
)
RETURN
IF(ISBLANK(PreviousJobMeterKM), BLANK(), CurrentJobMeterKM - PreviousJobMeterKM)

1. This measure calculates the difference in mileage between the current job and the previous job for the selected vehicle and job.

2. Difference in Date:

   Create another measure to calculate the difference in dates for each job:

Date Difference =
VAR CurrentJobDate = MAX(Jobs[JobDate])
VAR PreviousJobDate =
CALCULATE(
MAX(Jobs[JobDate]),
FILTER(
Jobs,
Jobs[Vehicle] = MAX(Jobs[Vehicle]) &&
Jobs[Job] = EARLIER(Jobs[Job]) &&
Jobs[JobDate] < EARLIER(Jobs[JobDate])
)
)
RETURN
IF(ISBLANK(PreviousJobDate), BLANK(), CurrentJobDate - PreviousJobDate)

1. This measure calculates the difference in days between the current job date and the previous job date for the selected vehicle and job.

Now, you can create tables or visuals in your Power BI report and use these measures to display the mileage and date differences for each job. Make sure to include appropriate filters for the selected vehicle and job.

Remember to adjust the table and column names in the DAX measures to match your actual data model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Water]

Hi, 

Thank you very much for your time. Really appreciate it!

Unfortunately something seems not right with your DAX from the first "Earlier" statement.

I recreated everything in this Power BI file here , including your DAX. Could you please have a look at it and let me know what we can do to get the DAX to work?

Again, I appreciate your time and expertise.

Best regards, 

W