Forum Discussion

BjoernSchaefer's avatar
BjoernSchaefer
Helper 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
Community Champion
7 years ago

BjoernSchaefer Could you please post the sample data (Copiable format) and expected output which will help to understand the issue bit more detail.

  • BjoernSchaefer's avatar
    BjoernSchaefer
    Helper 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
      Community 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
        Helper 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.