Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Get nth most recent date?

Hi all,

 

Does anyone know how to leverage DAX to get the nth most recent date in a series, given drop-down selection of currentDate and drop-down selection of numberPeriods?

 

Here's some sample data:

Quarter NameDate
Q1'203/31/2020
Q2'206/30/2020
Q3'209/30/2020
Q4'2012/31/2020
Q1'213/31/2021
Q2'216/30/2021
Q3'219/30/2021
Q4'2112/31/2021
Q1'223/31/2022
Q2'226/30/2022
Q3'229/30/2022
Q4'2212/31/2022
Q1'233/31/2023
Q2'235/31/2023

 

Note: Data will not always be quarterly. Any date is possible in the [Date] column. So, I do NOT want to use a pre-built Time Intelligence function like PREVIOUSMONTH() or PREVIOUSQUARTER(). It will fail if the intervals between the dates are not consistent, which is what I expect to happen. I'm getting stuck because I will not necessarily have a consistent delta between time intervals, so I can't really use functions like DATEADD() either.

 

So if the user selects 5/31/2023, and 3rd most recent date, the measure should return 9/30/22.

 

The only way I know to do this is have:

 

 

VAR currentDate = SELECTEDVALUE([Date])

VAR nextMostRecent = CALCULATE(MAX('Table'[Date]), ALL('Table'), 'Table'[Date] < currentDate)

VAR nextNextMostRecent = CALCULATE(MAX('Table'[Date]), ALL('Table'), 'Table'[Date] < nextMostRecent)

-- And so on for older and older dates

 

 

 

But this only works if it's the next most recent date to current date. To get older and older dates, I have to hardcode, which is not viable. How can I do this in a flexible manner? I need it so that the intervals between the dates is not considered, only the fact that it is n periods older - all dates can be assumed to be unique.

 

Thanks!

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    This seems to be the best way to do it. Thank you!