Forum Discussion

Water's avatar
Water
Icon for Helper II rankHelper II
3 years ago
Solved

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 milea...
DAX for dynamic difference calculations.
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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 )

    Best Regards

ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
3 years ago

Hi,

Here is one way to do this:

Data:

 

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:

 

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/
Water's avatar
Water
Icon for Helper II rankHelper II
2 years ago

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.

 

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.

 

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

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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 )

    Best Regards

    • Water's avatar
      Water
      Icon for Helper II rankHelper II
      2 years ago

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

       

       

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

       

       

       

      Is there maybe something else you could try?

       

      With hope,

       

      Water