Reversing Accumulative Source Data - (unique Id / month)

[TaylorTako7]

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 - July 2017 (null)

002 - 345 - July 2017 (null)

003 - 456 - July 2017 (null)

001 - 550 - August 2017 (50)

002 - 452 - August 2017 (207)

003 - 500 - August 2017 (44)

004 - 200 - August 2017 (null)

001 - 560 - September 2017 (10)

003 - 500 - September 2017 (0)

004 - 220 - September 2017 (20)

As you can see, some added complexity comes out when users are deleted and also added.

I basically want to add a column which will search for look for the most recent monthly data, deduct that away from the credits to get a DIFFERENCE between each month.. I've placed the expected values in the above data set in (brackets).

This is to view credits GAINED per MONTH per UNIQUE ID.

When there is no recent monthly unique ID, the row under this new column should read "null"

Same with when a user is added.

Looking forward to solution on this !

Many thanks.

[Phil_Seamark]

HI @TaylorTako7

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

[TaylorTako7]

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 · Accepted solution]

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]

Outstanding work. 

Thank you.