Forum Discussion
MrDarian
Helper II
Helper IIPrevious 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 ID -- One Task ID> many Sub task ID -- One task ID> one amount
3. calculate ratio based on previous row in each program ID
like image below:
Power BI File Link for download: https://gofile.io/d/oOa25R
------
Anonymous
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))RETURNSWITCH(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
1 Reply
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))RETURNSWITCH(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