cancel
Showing results for
Did you mean:
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])))

1 ACCEPTED SOLUTION
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

5 REPLIES 5
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

New Member

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

Thanks

Helper III

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

Super User III

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

Frequent Visitor

Great job!!! It works perfectly.

Announcements

#### Microsoft Business Applications Summit sessions

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