DAX - 2nd to last Date - FILTER Job Level

[lync0056]

Hi all,

I'm having trouble returning the 2nd to last MAX invoice date inside DAX when I am filtering in Matrix by Client with drilldown to JobID. (Just for reverence, it is 1 Table - Invoices. All Invoices have a Client allocated to it and a JobID. So a Client can have many JobIDs)

The Last Date is no issue

Last Invoice Date = LASTDATE ( Invoices[Date] )

(Context is August 2019)

The second to last date works inside the context for only the Client filter. It is blank for JobIDs.

2nd To Last Invoice Date = CALCULATE( MAX( Invoices[Date]), FILTER( Invoices, Invoices[Date] <> MAX( Invoices[Date])))

Above will work if 2 Invoices were raised in August. It will give me the one previous for the Client. The moment I leave the August Context with either ALL or ALLSELECTED, it either returns blank, or the 31/08/2019. 

2nd To Last Invoice Date = CALCULATE( MAX( Invoices[Date]), FILTER( ALLSELECTED(Invoices), Invoices[Date] <> MAX( Invoices[Date])))

Any help would be appreciated.

Cam

@v-lid-msft 

[lync0056 · Accepted solution]

Went a SQL path to solve this.

[v-chuncz-msft]

@lync0056 

It seems that you may change "<>" to "<".

[lync0056]

@v-chuncz-msft 

This doesn't seem to work. In August context, looking at 1 particular JobID, it returns 30/08/2019

It should be returning 31/07/2019, as this was the last invoice raised for this Job. 30/08/2019 makes no sense, as no invoice was raised on this date for this job. 

DAX

2nd To Last Invoice Date = CALCULATE( MAX( Invoices[Date]),

FILTER( ALL(Invoices), Invoices[Date] < MAX(Invoices[Date]))

)