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.
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 ðŸ‘)