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)

     

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.