Find previous dates of selected date

[clubspec]

Hi Gurus,

I need to find the previous dates based on selected date.

I created a measure: Last Insp Date = CALCULATE(MAX('TABLE'[Date]), 'TABLE'[Date] < MAX('TABLE'[Date])) this bit works fine, when I select a date from a slicer.

Then I created another measure to show the 2nd Last Insp Date = CALCULATE(MAX('TABLE'[Date]), FILTER(ALL('TABLE'), 'TABLE'[Date] < [Last Insp Date])) I used the previous measure I created as reference but this returns (Blank), I expect it would return 25/09/23 as picture below.  What did I do wrong?

Thank you

[rajendraongole1]

Hi @clubspec -create the first calculation as below

First Last Inspection Date:

Last Insp Date = CALCULATE(MAX('TABLE'[Date]), 'TABLE'[Date] < MAX('TABLE'[Date]))

Adjust your second Last Inspection Date as expected result 

2nd Last Insp Date =
CALCULATE(
MAX('TABLE'[Date]),
FILTER(
ALL('TABLE'),
'TABLE'[Date] < CALCULATE(MAX('TABLE'[Date]), 'TABLE'[Date] < MAX('TABLE'[Date]))
)
)

Hope it works 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[clubspec]

Thank you rajendraongole1,

It works!  Now if I want to calculate the 3rd Last Insp Date do I have to repeat the very long DAX to return 24/09/23?

[rajendraongole1 · Accepted solution]

Hi @clubspec - yes, to calculate the 3rd Last Inspection Date, you would need to extend the DAX formula to account for the previous inspection dates. 

3rd Last Insp Date =
CALCULATE(
MAX('TABLE'[Date]),
FILTER(
ALL('TABLE'),
'TABLE'[Date] < [2nd Last Insp Date]
)
)

you can easily extend the logic to calculate the 4th, 5th, and nth Last Inspection Dates as needed by following the same pattern.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!