Forum Discussion
Calculate difference with cumulative total
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!
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
3 Replies
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
Wonderful!
Do not hesitate if you have any more questions,
LC