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 last two years. The Date ist always the first of the month. The Problem is: The database only delivers cumulative values by month and not the actual values for each month. Now i need to "un-cumulate" the values for the months. Has anybody got an idea how to solve this? See some example data below and thank you very much for every response i'm gettin'.

 

Kind regards

 

BjoernSchaefer

 

 

 

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

6 Replies

  • 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