topic Re: Calculate Difference between LASTDATE and 2nd to last date in DAX Commands and Tips

Calculate Difference between LASTDATE and 2nd to last date

mwc — Mon, 14 Oct 2019 21:11:47 GMT

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

Re: Calculate Difference between LASTDATE and 2nd to last date

AlB — Mon, 14 Oct 2019 22:31:30 GMT

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

Re: Calculate Difference between LASTDATE and 2nd to last date

mwc — Tue, 15 Oct 2019 00:27:12 GMT

Great job!!! It works perfectly.

Re: Calculate Difference between LASTDATE and 2nd to last date

alexbalazsalex — Fri, 06 Mar 2020 13:39:54 GMT

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

Re: Calculate Difference between LASTDATE and 2nd to last date

AlB — Sun, 08 Mar 2020 13:18:29 GMT

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

Re: Calculate Difference between LASTDATE and 2nd to last date

lfdominguez10 — Tue, 16 Feb 2021 23:49:19 GMT

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

Thanks

Re: Calculate Difference between LASTDATE and 2nd to last date

Anonymous — Thu, 19 May 2022 00:27:32 GMT

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.