Display difference between a value and its previous value

DAX DAX Calculations
1

Hi,

(This is different ask of a previous question with a new dataset and accompanying Power BI file to cover a lower level of detail for drill-down to week level, and not only to month level. The solutions offered previously could not accommodate the week-level drill-down.)

For each vehicle many jobs are performed at various mileage intervals. Each time a job is performed, the mileage (Meter KM) is captured. Below in the table you can see the mileage 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 mileage when each job was performed as you can see in the below table, but rather the difference between each value (km) and its previous value (km) for each job. This is to see clearly the interval in which the jobs were performed. This means e.g. for the first Job 06-02-005, 2021 June should not display147,584, but rather (147,584-136,011)= 11,573, and 2022 July should not display the 162,981, but rather (162,981-147,584=) 15,397.

**me**ter1

The aim is to see something like the below, but with the option to drill down to at least week level.

image

All the data is captured in the same WO_JOBS table. Here is sample data from this table.
The data captured in Power BI is here with a Melissa’s extended date table.

PLEASE NOTE: the interval between the various jobs differs. Some happen every two weeks, others once a year, and everything in between. The DAX should allow display/drill-down to week level, and not only month level. (Jobs ending with BIW are bi-weekly jobs.)

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

With thanks,

William

2

William
in your previous question, the measures were designed on a monthly basis
the calendar table has fields to group on weekly level
so you have to add measures for the weekly calculation
with the screenshots as result
the visual doesn’t say much in my opinion

kind regards Roger

image
image764×462 29.5 KB

image

3

Thank you very much, Roger.

Your feedback makes sense, but I can not get it to work with the measures I am using. (Believe me, that is frustrating!)

Here is it till month level in the Power BI file. I realise I am asking a lot, but will greatly appreciate it if you can please help me to change it to reflect your suggestion and upload the file again? Or just paste the DAX in a reply?

One comment/question: There are a couple of jobs like with unit 11301 where jobs occur every two weeks (Job 06-24-BIW). Only for these would we need to drill down to week level. For the rest month level is good. Would changing the DAX to use weeks allow role up to month level?

With great appreciation,

William.

PS. Here the DAX as in the provided pbix-file:

CurrentKm = SUM ( ‘WO_JOBS’[METER_KM])

PrevKmMoth =
CALCULATE (
[CurrentKm],
WINDOW (
-1,
REL,
-1,
REL,
FILTER (
ALL ( ‘Calendar’[Year], ‘Calendar’[Month], ‘Calendar’[Month Name] ),
[CurrentKm] <> BLANK ()
)
)
)

PrevKmYear =
CALCULATE (
[CurrentKm],
WINDOW (
-1,
REL,
-1,
REL,
FILTER ( ALL ( ‘Calendar’[Year] ), [CurrentKm] <> BLANK () )
)
)

PreviousKm =
IF ( ISINSCOPE ( ‘Calendar’[Month Name] ), [PrevKmMoth], [PrevKmYear] )

DifferenceKm =
SUMX (
FILTER( SUMMARIZECOLUMNS (WO_JOBS[JOB], ‘Calendar’[Year], ‘Calendar’[Month Name]), [PreviousKm] <> BLANK() ),
[CurrentKm] - [PreviousKm]
)

4

question 1 : there is no significant drop in performance using the calculated column. I suppose the fact table has not million of rows.
question 2 : this error occurs when the earlier () doesn’t find a previous date.
you can use the isblank() to avoid the error. Use the calculate () to wrap the code for the calculated column.
question 3: if it works for calculation the difference in days it will also work for calculating the difference in km just change the last line of code .

I will look at it when I have some more time, very busy at the moment
kind regards

Roger

5

Thank you, Roger, highly appreciate your feedback and time!

Will patiently await your follow-up.

If I may:

Question 2. Please help to compose a measure version of the calculated column? I cannot get it to work.

Question 3. The below seems to work (last line changed to give mileage difference and not days difference). Does it look okay to you? (This mileage difference would be a new and improved answer to the current post here where you posted this reply. The days difference was the answer to this post.)

====================================================================
DIFF in mileage =
var temp =
TOPN (
1,
FILTER (
WO_JOBS,
WO_JOBS[JOB] = EARLIER ( WO_JOBS[JOB] ) &&
WO_JOBS[UNIT_NO]=EARLIER(WO_JOBS[UNIT_NO]) &&
WO_JOBS[UNIT_NO]=EARLIER(WO_JOBS[UNIT_NO]) &&
WO_JOBS[JOB_OPEN_DATE] < earlier(WO_JOBS[JOB_OPEN_DATE] )
),
WO_JOBS[JOB_OPEN_DATE], DESC
)
return
----DATEDIFF( minx(temp,[JOB_OPEN_DATE]),WO_JOBS[JOB_OPEN_DATE] ,DAY)
IF(ISEMPTY(temp),BLANK(),WO_JOBS[Meter_KM]-Minx(temp,WO_JOBS[Meter_KM]))

========================================================================

Please note: the three questions you answered were originally posted here as part of days difference question.

Thanks and best regards,

William

6

Wiliam
the earlier function is not a good practice in measure, I learned from Greg, he proposed another solution.
I added a column weeknr, so you check the numbers ( days and KM ) delete it afterward. The pattern is relative simple and I copied it for the KM difference calculation.

hope this is what you are looking for Don’t forget to check the solution button
kind regards

Roger
KM difference and difference in days .pbix (247.2 KB)

7

Wiliam

did you have the time to look at the Pbix file ( solution for your problem )?

kind regards
Roger

8

Thank you so much, Roger!

I will have a look and get back to you, probably early next week. Looking at your DAX wizardry with your previous responses I am sure this will be the solution ever.

Will return to confirm and accept as solution soon.

Be assured of my greatest appreciation of your time and expertise.

Best wishes,

William