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
    )

     

johnt75's avatar
johnt75
Super User
1 year ago

Try

Gen Difference =
VAR FirstEntry =
    INDEX (
        1,
        ALLSELECTED ( mMjIms8 ),
        ORDERBY ( mMjIms8[Created At], DESC ),
        PARTITIONBY ( mMjIms8[Vehicle Plate No] ),
        MATCHBY ( mMjIms8[Vehicle Plate No] )
    )
VAR SecondEntry =
    INDEX (
        2,
        ALLSELECTED ( mMjIms8 ),
        ORDERBY ( mMjIms8[Created At], DESC ),
        PARTITIONBY ( mMjIms8[Vehicle Plate No] ),
        MATCHBY ( mMjIms8[Vehicle Plate No] )
    )
VAR FirstMileage =
    SELECTCOLUMNS ( FirstEntry, mMjIms8[Current Mileage] )
VAR SecondMileage =
    SELECTCOLUMNS ( FirstEntry, mMjIms8[Current Mileage] )
VAR Result = FirstMileage - SecondMileage
RETURN
    Result