Forum Discussion

erichock's avatar
erichock
New Member
1 year ago
Solved

Dynamic Row Calculation

Hi, I am having a brain block, trying to get this to work. I am trying to get the difference between each set of 'vehicle plate no', current mileage (based on rank). Entries of the same 'vehicle pl...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    OFFSET function (DAX) - DAX | Microsoft Learn

     

    expected result measure: =
VAR _currentmileage =
    SUM ( mMjIms8[current_mileage] )
VAR _previous =
    CALCULATE (
        SUM ( mMjIms8[current_mileage] ),
        OFFSET (
            -1,
            SUMMARIZE (
                ALL ( mMjIms8 ),
                mMjIms8[vehicle_plate_no],
                mMjIms8[date],
                mMjIms8[current_mileage]
            ),
            ORDERBY ( mMjIms8[date], ASC ),
            ,
            PARTITIONBY ( mMjIms8[vehicle_plate_no] )
        )
    )
RETURN
    IF (
        NOT ISBLANK ( _previous ) && HASONEVALUE ( mMjIms8[vehicle_plate_no] ),
        _currentmileage - _previous
    )

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
1 year ago

Hi,

Please check the below picture and the attached pbix file.

 

 

OFFSET function (DAX) - DAX | Microsoft Learn

 

expected result measure: =
VAR _currentmileage =
    SUM ( mMjIms8[current_mileage] )
VAR _previous =
    CALCULATE (
        SUM ( mMjIms8[current_mileage] ),
        OFFSET (
            -1,
            SUMMARIZE (
                ALL ( mMjIms8 ),
                mMjIms8[vehicle_plate_no],
                mMjIms8[date],
                mMjIms8[current_mileage]
            ),
            ORDERBY ( mMjIms8[date], ASC ),
            ,
            PARTITIONBY ( mMjIms8[vehicle_plate_no] )
        )
    )
RETURN
    IF (
        NOT ISBLANK ( _previous ) && HASONEVALUE ( mMjIms8[vehicle_plate_no] ),
        _currentmileage - _previous
    )

 

erichock's avatar
erichock
New Member
1 year ago

Thanks Kim,

I got it to work with your help 🙏