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 value (km) and its previous value (km) per selection

Hi, 

 

Please help? I really struggle to create dax to accomplish the following:

 

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 display (147,584-136,011)= 11,573, and for 2022 July (162,981-147,584=) 15,397.

 

meter1.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. 

 

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

1 ACCEPTED SOLUTION

Hi @Water ,

You can update the formula of measure [Value diff] as below and check if it can return the correct result...

Value diff = 
VAR _date =
    MAX ( 'WO_JOBS'[JOB_OPEN_DATE_USE] )
VAR _predate =
    CALCULATE (
        MAX ( 'WO_JOBS'[JOB_OPEN_DATE_USE] ),
        FILTER ( ALLSELECTED ( 'WO_JOBS' ), 'WO_JOBS'[JOB_OPEN_DATE_USE] < _date )
    )
VAR _lastValue =
    CALCULATE (
        MAX ( 'WO_JOBS'[Meter_KM] ),
        FILTER ( ALLSELECTED ( 'WO_JOBS' ), 'WO_JOBS'[JOB_OPEN_DATE_USE] = _predate )
    )
VAR _value =
    SUM ( 'WO_JOBS'[METER_KM] )
RETURN
    IF ( ISBLANK ( _lastValue ), BLANK (), _value - _lastValue )

vyiruanmsft_0-1698144539392.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
ValtteriN
Super User
Super User

Hi,

Here is one way to do this:

Data:

ValtteriN_1-1696313893085.png

 

Dax: (add calendar table to your model if you don't have one)

Value diff =
var _date = MAX('Table'[Date])
Var _lastValue = CALCULATE(MAX('Table'[value]),ALL('Calendar'),'Calendar'[Date]<_date)
 var _value = MAX('Table'[value]) return
IF(ISBLANK(_lastValue),BLANK(),_value-_lastValue)

End result:
ValtteriN_0-1696316566838.png

 

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi, 

Thank you again for your help! I am very excited about your solution!

 

Picked up just one issue as below:

 

If a previous km entry was (admittedly incorrect) as below (the 5580 for Dec 2021), the resultant difference in this instance should be 101,788 - 5,580 = 96,208.

 

Correct min.jpg

However, you will see in the below screenshot, using your DAX it displays (incorrectly) as 14,338, which looks like it comes from 96,208-81,870.

 

Current incorrect.jpg

Question: Is there an update to the DAX possible to get rid of this small error?

 

The Power BI file is here, and the Excel dataset here.

 

(Footnote: Sorry for the slow reply. I am still learning and had to spend time to better understand the date table. Could not initially get your solution to work as I had incompatible date formats. Also, did not know about the about the "Sort by column" option.  Now that I understand this better, your DAX seems to work great (other than just the little issue above!)

 

Thanks and best regards, 

 

W

 

 

 

Hi @Water ,

You can update the formula of measure [Value diff] as below and check if it can return the correct result...

Value diff = 
VAR _date =
    MAX ( 'WO_JOBS'[JOB_OPEN_DATE_USE] )
VAR _predate =
    CALCULATE (
        MAX ( 'WO_JOBS'[JOB_OPEN_DATE_USE] ),
        FILTER ( ALLSELECTED ( 'WO_JOBS' ), 'WO_JOBS'[JOB_OPEN_DATE_USE] < _date )
    )
VAR _lastValue =
    CALCULATE (
        MAX ( 'WO_JOBS'[Meter_KM] ),
        FILTER ( ALLSELECTED ( 'WO_JOBS' ), 'WO_JOBS'[JOB_OPEN_DATE_USE] = _predate )
    )
VAR _value =
    SUM ( 'WO_JOBS'[METER_KM] )
RETURN
    IF ( ISBLANK ( _lastValue ), BLANK (), _value - _lastValue )

vyiruanmsft_0-1698144539392.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, 

Thank you very much for trying to help!

 

Unfortunately this is correcting that one issue, but now most of the other figures are wrong. 

 

Only the figures in red below are correct. All the others are now incorrect. With the previous DAX version higher up in this post all figures except that one was correct (see post above).

 

DAX new problems.jpg

 

If we can get a DAX that is working perfectly, the output will look like the below screenshot I made in Excel.

 

 

Solution I am hoping for.jpg

 

Is there maybe something else you could try?

 

With hope,

 

Water

Hi, 

 

Thank you so much for trying to help! Really appreciate it!

 

I added a calendar/date table and created your measure, but unfortunately it seems not to work? 

 

Would you mind to please have a look at this pbix file with a small subset of data here? Not sure if it has to do with how my date table connects to the data table, but the selecting any date/year from the calender table produces absolute nothing to display over any time span selected.

 

Thanks again!

 

W

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors