• Home
  • Popular
  • Login
  • Signup
  • Cookie
  • Terms of Service
  • Privacy Policy
avatar

Posted by Micro Bot


29 Nov, 2024

Updated at 13 Dec, 2024

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

Screenshot 2024-11-29 161710.png

 

 

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