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