Calculate Difference between LASTDATE and 2nd to last date

[mwc]

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])))

[Anonymous]

Hi! I have this kind of problem in Power BI. I have a change number and status. As long as the last User Name is Change Analyst - Manila, I need to get the date difference from the previous date (second to the last date - this will be my start date). My end date would be the date from Change Analyst - Manila.

[AlB · Accepted solution]

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  

[lfdominguez10]

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

Thanks

[alexbalazsalex]

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

[AlB]

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  

[mwc]

Great job!!! It works perfectly.