Forum Discussion

TaylorTako7's avatar
TaylorTako7
Frequent Visitor
8 years ago
Solved

Reversing Accumulative Source Data - (unique Id / month)

Hi,   So my source data works is working in credits for each person per month, data in 3 columns looks like:   User ID - Credit Points Running Total - Data Month: (Added column)   001 - 500 - J...
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi TaylorTako7

     

    I didn't read your original post properly.  Please try this calculated column

     

    New Column = 
VAR LastMonthValue =  
    SUMX(
        FILTER(
            'Table2',
            'Table2'[User ID] = EARLIER('Table2'[User ID]) &&
            'Table2'[Data Month] = EDATE(EARLIER('Table2'[Data Month]),-1)
            ),
            'Table2'[Credit Points Running Total]
            )
RETURN   IF(NOT ISBLANK(LastMonthValue),'Table2'[Credit Points Running Total]-LastMonthValue)

     

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

HI TaylorTako7

 

So for that sample dataset, can you please post your expected outcome?  This will help clarify you requirements :)

TaylorTako7's avatar
TaylorTako7
Frequent Visitor
8 years ago

Hi Phil,

 

My expected outcome results are detailed in the (brackets) that i've supplied in the data set.

 

Once I get to this i'll need to add a number of formulas including "Expected credits" and "Expected credits vs Actual credits", as each month there will be a set number. But I don't need assistance on this.  I just need help getting to what is listed in the (brackets) above.

 

Thanks

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi TaylorTako7

     

    I didn't read your original post properly.  Please try this calculated column

     

    New Column = 
VAR LastMonthValue =  
    SUMX(
        FILTER(
            'Table2',
            'Table2'[User ID] = EARLIER('Table2'[User ID]) &&
            'Table2'[Data Month] = EDATE(EARLIER('Table2'[Data Month]),-1)
            ),
            'Table2'[Credit Points Running Total]
            )
RETURN   IF(NOT ISBLANK(LastMonthValue),'Table2'[Credit Points Running Total]-LastMonthValue)

     

    • TaylorTako7's avatar
      TaylorTako7
      Frequent Visitor
      8 years ago

      Outstanding work. 

       

      Thank you.