cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Total difference from previous date

Hi all,

 

I have 2 columns, one with values and the other with dates. The dates are non continous , for e.g. 10/10/2019, 12/10/2019, 20/10/2019 etc

 

I need to be able to get the difference between the sum of all the values from each date and previous date.

 

Is there a way to accomplish this?

 

I already have a column with the previous date gotten with this formula: 

Predate = CALCULATE (
    MAX (WeeklyOutstanding[Week] ), FILTER ( WeeklyOutstanding, WeeklyOutstanding[Week] < EARLIER ( WeeklyOutstanding[Week] ) ) )

 

Help would be greatly appreciated 

1 ACCEPTED SOLUTION
Super User I
Super User I

Hi @Rm666652,

 

You can try measure like the following

 

 

Delta From Previous = 
    var prev_date = CALCULATE(MAX('WeeklyOutstanding'[Week]), FILTER(all('WeeklyOutstanding'), 'WeeklyOutstanding'[Week] < SELECTEDVALUE('WeeklyOutstanding'[Week])))
    return
    sum('WeeklyOutstanding'[amount]) - CALCULATE(SUM('WeeklyOutstanding'[amount]), FILTER(ALL('WeeklyOutstanding'), 'WeeklyOutstanding'[Week] = prev_date))

 

 

Hope that Helps

Richard





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Frequent Visitor

This worked great! Thank you!

 

The  only issue now is that somehow it only works with the full data, whenever I use filters its gives the wrong amounts. Do you know why?

It is removing the filter context with

all('WeeklyOutstanding'[Week])

, you may need to change that be just 

ALL('WeeklyOutstanding')

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Super User I
Super User I

Hi @Rm666652,

 

You can try measure like the following

 

 

Delta From Previous = 
    var prev_date = CALCULATE(MAX('WeeklyOutstanding'[Week]), FILTER(all('WeeklyOutstanding'), 'WeeklyOutstanding'[Week] < SELECTEDVALUE('WeeklyOutstanding'[Week])))
    return
    sum('WeeklyOutstanding'[amount]) - CALCULATE(SUM('WeeklyOutstanding'[amount]), FILTER(ALL('WeeklyOutstanding'), 'WeeklyOutstanding'[Week] = prev_date))

 

 

Hope that Helps

Richard





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors