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

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.