Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ttseng
Helper III
Helper III

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!

1 ACCEPTED SOLUTION
lc_finance
Solution Sage
Solution Sage

Hi @ttseng ,

 

 

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:

Decumulated savings.png

 

Does this help you?

 

LC

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

View solution in original post

3 REPLIES 3
lc_finance
Solution Sage
Solution Sage

Hi @ttseng ,

 

 

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:

Decumulated savings.png

 

Does this help you?

 

LC

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

hi @lc_finance 

 

thank you! Your solution worked perfectly! 

Wonderful!

 

Do not hesitate if you have any more questions,

 

LC

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.