Dynamic Row Calculation

[erichock]

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 plate no' will increase each month. Therefore the "Gen Difference" DAX needs to be dynamic.

This is an example of what I am trying to achieve.

DAX(Measure)

• Rank = RANKX(FILTER('mMjIms8','mMjIms8'[Vehicle Plate No]=EARLIER('mMjIms8'[Vehicle Plate No])),'mMjIms8'[Created At],,DESC,Dense)
• Gen Difference =

  VAR _RANK1GenHours =

      CALCULATE (

          SUM ( mMjIms8[Current Mileage]),

          FILTER (

              ALL ( mMjIms8 ),

              'mMjIms8'[Vehicle Plate No] = MAX ( 'mMjIms8'[Vehicle Plate No])

                  && 'mMjIms8'[Rank] = 1

          )

      )

  VAR _RANK2GenHours =

      CALCULATE (

          SUM ( mMjIms8[Current Mileage]),

          FILTER (

              ALL ( mMjIms8 ),

              'mMjIms8'[Vehicle Plate No] = MAX ( 'mMjIms8'[Vehicle Plate No])

                  && 'mMjIms8'[Rank] = 2

          )

      )

      RETURN

      IF (

          SUM ( 'mMjIms8'[Rank] ) = 1,

          IF ([_RANK1GenHours] = ABS( _RANK1GenHours - _RANK2GenHours ),Blank() ,ABS( _RANK1GenHours - _RANK2GenHours )),

          BLANK ()

      )

Rank's DAX is working perfectly fine but I couldn't get "VAR _RANK2GenHours" to automatically deduct 1 on each row.

I have tried "&& 'mMjIms8'[Rank] = 'mMjIms8'[Rank]-1, but this results in the entire column being blank.

What am I missing over here? 🙏
(Your help is much appreciated over here 👍)

[Jihwan_Kim · Accepted solution]

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]

Thanks Kim,

I got it to work with your help 🙏

[erichock]

Thanks Kim,

I got this to work with your solution. 🤝

[johnt75]

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