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

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

AccountDateValue CumulativeDescriptionExpected OutcomeDescription2

175301.01.2017157<- These are the running totals by month157 
175301.01.2017152 152 
175301.01.2017177 177 
175301.01.2017120 120 
175301.01.2017138 138 
175301.01.2017129 129 
175301.01.2017141 141 
175301.01.2017118 118 
175301.01.2017175 175 
144601.01.2017137 137 
144601.01.2017181 181 
144601.01.2017165 165 
144601.01.2017114 114 
144601.01.2017165 165 
144601.01.2017180 180 
144601.01.2017115 115 
144601.01.2017128 128 
144601.01.2017181 181 
144601.01.2017182 182 
144601.01.2017190 190 
175302.01.2017301 144<- This is the value i need. It's the difference to January 1st.
175302.01.2017273 121 
175302.01.2017367 190 
175302.01.2017270 150 
175302.01.2017328 190 
175302.01.2017267 138 
175302.01.2017307 166 
175302.01.2017229 111 
175302.01.2017364 189 
144602.01.2017297 160 
144602.01.2017360 179 
144602.01.2017296 131 
144602.01.2017271 157 
144602.01.2017269 104 
144602.01.2017302 122 
144602.01.2017289 174 
144602.01.2017326 198 
144602.01.2017371 190 
144602.01.2017336 154 
144602.01.2017304 114 
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.