Forum Discussion

BjoernSchaefer's avatar
BjoernSchaefer
Icon for Helper II rankHelper II
7 years ago
Solved

Un-cumulate Cumulative Values

Hello Everybody,     I'm dealing with a little issue right now. I have a datamodel containing a Fact-Table with several Dimension-Tables. The Fact-Table contains transactions for Accounts over the...
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    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's avatar
PattemManohar
Icon for Community Champion rankCommunity Champion
7 years ago

BjoernSchaefer Just to confirm, is this what you are expecting as output in a new table

 

1753 01.01.2017 157
1753 02.01.2017 144

 

Or you want to display in new column as mentioned above

BjoernSchaefer's avatar
BjoernSchaefer
Icon for Helper II rankHelper II
7 years ago

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.

  • PattemManohar's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      BjoernSchaefer
      Icon for Helper II rankHelper II
      7 years ago

      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.