Calculate difference with cumulative total

[Anonymous]

I have a dataset that has a running total (cumulative total) but need to be able to create a column that tracks the difference by date and category. Everything I've found online seems to show how to create the cumulative column and not the other way round.

The calculated column i'm trying to create is "Value Savings" below:

As you can see I need to determine the earliest value for a Group (uniqueID) and then have that cumulative savings be the starting point to calculate the difference.

'Dataset for dashboard'

uniqueID	Date	Cumulative Savings	Value Savings
Group A	6/1/2016	$50.00	$50.00
Group B	6/1/2016	$120.00	$120.00
Group A	10/01/2016	$222.00	$172.00
Group B	10/01/2016	$200.00	$80.00
Group A	3/01/2017	$300.00	$78.00
Group B	3/01/2017	$233.00	$33.00
Group A	6/01/2017	$400.00	$100.00
Group B	6/01/2017	$289.00	$56.00

I've tried the following:

Value Savings = 
VAR Prev =
CALCULATE(
    SUM('Dataset for Dashboard'[Cumulative Savings]),
    FILTER(
        ALL('Dataset for Dashboard'),
        'Dataset for Dashboard'[Date]
        = EARLIER('Dataset for Dashboard'[Date]) - 1
        && 'Dataset for Dashboard'[uniqueID] = EARLIER( 'Dataset for Dashboard'[uniqueID])
    )
)
Return
'Dataset for Dashboard'[Cumulative Savings] - Prev

However this returns the same value and I'm pretty sure it's because it's set as -1 in the following line:

= EARLIER('Dataset for Dashboard'[Date]) - 1

Am I on the right track? I cannot for the life of me figure this out.

Thanks!

[lc_finance · Accepted solution]

Hi @Anonymous ,

You can download my proposed solution from here.

Here is the formula for Value Savings:

Value savings = 
var currentDate = SELECTEDVALUE(Savings[Date])
var priorDate = CALCULATE(LASTDATE('Savings'[Date]), ALLEXCEPT('Savings','Savings'[uniqueID]),'Savings'[Date]<currentDate)
RETURN 
SUM('Savings'[Cumulative Savings]) - CALCULATE(SUM('Savings'[Cumulative Savings]), ALLEXCEPT('Savings',Savings[uniqueID]), 'Savings'[Date]=priorDate)

this is how the formula works:

first, it finds the current date

second, it finds the prior date for the same uniqueID

finally, it calculates the difference between the cumulated savings on the current date and the cumulated savings on the prior date.

Here is a screenshot of the final result:

Does this help you?

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

[Anonymous]

hi @lc_finance 

thank you! Your solution worked perfectly! 

[lc_finance]

Wonderful!

Do not hesitate if you have any more questions,

LC