Forum Discussion

MrDarian's avatar
MrDarian
Icon for Helper II rankHelper II
6 years ago
Solved

Previous row by DAX

I need a DAX Code for calculating Previous row. points: 1. For the first one amount for each program id, it must be 100% because it has not any data before 2. Ralations: One program ID> many task I...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi MrDarian ,

     

    I think there are multiple threads opened to this question.

     

    Pleae find the solution.

     

    RatioMeasure =

    var _minsub = CALCULATE(MIN('Table'[Sub ID ]), FILTER(ALL('Table'),'Table'[PID]= MAX('Table'[PID]) && 'Table'[ID] = MAX('Table'[ID])))
    var _maxsubid = CALCULATE(MIN('Table'[Amount]),FILTER(ALL('Table'),'Table'[PID]= MAX('Table'[PID]) && 'Table'[ID] = MAX('Table'[ID])-1 && 'Table'[Sub ID ] = MAXX(ALL('Table'),'Table'[Sub ID ])))
    var _previousamount = CALCULATE(MAX('Table'[Amount]), FILTER(ALL('Table'),'Table'[PID]= MAX('Table'[PID])&&'Table'[ID] = MAX('Table'[ID]) && 'Table'[Sub ID ] = MAX( 'Table'[Sub ID ]) -1))

    RETURN
    SWITCH(
    TRUE(),
     
    MAX('Table'[Sub ID ]) = _minsub && MAX('Table'[ID]) = MINX(ALL('Table'[ID]),'Table'[ID]), DIVIDE(MAX('Table'[Amount]),MAX('Table'[Amount])),
    MAX('Table'[Sub ID ]) = _minsub, DIVIDE(MAX('Table'[Amount]),_maxsubid),
    DIVIDE(MAX('Table'[Amount]),_previousamount)
    )
     
     
    Regards,
    Harsh Nathani
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi MrDarian ,

 

I think there are multiple threads opened to this question.

 

Pleae find the solution.

 

RatioMeasure =

var _minsub = CALCULATE(MIN('Table'[Sub ID ]), FILTER(ALL('Table'),'Table'[PID]= MAX('Table'[PID]) && 'Table'[ID] = MAX('Table'[ID])))
var _maxsubid = CALCULATE(MIN('Table'[Amount]),FILTER(ALL('Table'),'Table'[PID]= MAX('Table'[PID]) && 'Table'[ID] = MAX('Table'[ID])-1 && 'Table'[Sub ID ] = MAXX(ALL('Table'),'Table'[Sub ID ])))
var _previousamount = CALCULATE(MAX('Table'[Amount]), FILTER(ALL('Table'),'Table'[PID]= MAX('Table'[PID])&&'Table'[ID] = MAX('Table'[ID]) && 'Table'[Sub ID ] = MAX( 'Table'[Sub ID ]) -1))

RETURN
SWITCH(
TRUE(),
 
MAX('Table'[Sub ID ]) = _minsub && MAX('Table'[ID]) = MINX(ALL('Table'[ID]),'Table'[ID]), DIVIDE(MAX('Table'[Amount]),MAX('Table'[Amount])),
MAX('Table'[Sub ID ]) = _minsub, DIVIDE(MAX('Table'[Amount]),_maxsubid),
DIVIDE(MAX('Table'[Amount]),_previousamount)
)
 
 
Regards,
Harsh Nathani