Forum Discussion
Un-cumulate Cumulative Values
BjoernSchaefer Here is the steps I've followed to solve this.
Step 1 : Add an Index Column (start from 1) in "Power Query"
Step 2 : Add an additional column using "New Column" as below
RankAccount = RANKX(FILTER(Test04UnCumm,Test04UnCumm[Account]=EARLIER(Test04UnCumm[Account]) && Test04UnCumm[Date] = EARLIER(Test04UnCumm[Date])),Test04UnCumm[Index],,ASC)
Step 3 : Finally, here is the magical column we are looking for
UnCummVal = VAR _PrevCummVal = LOOKUPVALUE(Test04UnCumm[CummVal],Test04UnCumm[Account],Test04UnCumm[Account],Test04UnCumm[Date],Test04UnCumm[Date]-1,Test04UnCumm[RankAccount],Test04UnCumm[RankAccount]) RETURN IF(_PrevCummVal=BLANK(),Test04UnCumm[CummVal],Test04UnCumm[CummVal] - _PrevCummVal)
The output will be as below :
Uhhh !! Nice one :-)
PattemManohar, this is correct. I need an extra column. But i'm not able to figure out how to do that. Thank you very much for your responses.
BjoernSchaefer Here is the steps I've followed to solve this.
Step 1 : Add an Index Column (start from 1) in "Power Query"
Step 2 : Add an additional column using "New Column" as below
RankAccount = RANKX(FILTER(Test04UnCumm,Test04UnCumm[Account]=EARLIER(Test04UnCumm[Account]) && Test04UnCumm[Date] = EARLIER(Test04UnCumm[Date])),Test04UnCumm[Index],,ASC)
Step 3 : Finally, here is the magical column we are looking for
UnCummVal = VAR _PrevCummVal = LOOKUPVALUE(Test04UnCumm[CummVal],Test04UnCumm[Account],Test04UnCumm[Account],Test04UnCumm[Date],Test04UnCumm[Date]-1,Test04UnCumm[RankAccount],Test04UnCumm[RankAccount]) RETURN IF(_PrevCummVal=BLANK(),Test04UnCumm[CummVal],Test04UnCumm[CummVal] - _PrevCummVal)
The output will be as below :
Uhhh !! Nice one :-)
PattemManohar, this is it. I already tried that one with the lookupvalue and the index but hadn't got the clue to use a rank. That was the missing link. Thank you so very much. I really appreciate it.