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
3 years ago

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