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

Calculate Difference between LASTDATE and 2nd to last date

Hello,

I am looking to calculate the difference of the SUM of values between the LASTDATE rows, and the rows from the 2nd most recent date (note that the dataset dates do not consist of consecutive days).

 

The calculation I have for SUM Last Date is working:

SUM Last Date = CALCULATE(SUM(Sheet1[NET_OPEN]),LASTDATE('Sheet1'[Date]))
 
The calculation I have for generating the second to last date is working:
2nd Last Date = FORMAT(CALCULATE(MAX(Sheet1[Date]),FILTER(Sheet1, 'Sheet1'[Date]<>MAX(Sheet1[Date]))),"MM/DD/YYYY")
- just as a side note, I formatted the date figure it to make sure the date format matched the date format in the table  that I want to filter. 
 
The formula that is not working is the SUM of 2nd last date:
SUM 2ND LAST DATE = CALCULATE(SUM(Sheet1[NET_OPEN]),FILTER(Sheet1,(Sheet1[2nd Last Date])))
 
2nd last date.JPG
 
1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

Hi @mwc 

First, don't convert the date to text, lose the FORMAT(). I'm assuming your date column is of Date type of course:

2nd Last Date V2 =
CALCULATE (
    MAX ( Sheet1[Date] ),
    FILTER ( Sheet1, 'Sheet1'[Date] <> MAX ( Sheet1[Date] ) )
)

and then:

SUM 2ND LAST DATE =
VAR SecondLast_ = [2nd Last Date V2] // Your measure V2. Best practice is  to not use table name with measures
RETURN
    CALCULATE ( SUM ( Sheet1[NET_OPEN] ), Sheet1[Date] = SecondLast_ )

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

View solution in original post

5 REPLIES 5
AlB
Super User III
Super User III

Hi @mwc 

First, don't convert the date to text, lose the FORMAT(). I'm assuming your date column is of Date type of course:

2nd Last Date V2 =
CALCULATE (
    MAX ( Sheet1[Date] ),
    FILTER ( Sheet1, 'Sheet1'[Date] <> MAX ( Sheet1[Date] ) )
)

and then:

SUM 2ND LAST DATE =
VAR SecondLast_ = [2nd Last Date V2] // Your measure V2. Best practice is  to not use table name with measures
RETURN
    CALCULATE ( SUM ( Sheet1[NET_OPEN] ), Sheet1[Date] = SecondLast_ )

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

View solution in original post

Hi guys, if i want to do the same but with the third and fourth date what can i do?

Thanks

Hi @AlB ,

 

Your formula seems to be working for my file , but when i put in my matrix the columns it is showing only the Last month amount.

 

If i reamove the split by year it shows the sum of the multiple years and the delta as last to .

 

IS there a way around it ?

 

Thanks

Alex

Hi @alexbalazsalex 

I'm not sure I understand the question. Can you show an example to illustrate the problem (ideally on a .pbix)?

Please mark the question solved when done and consider giving kudos if posts are helpful.

 

Cheers  Datanaut

mwc
Frequent Visitor

Great job!!! It works perfectly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors