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 Name | Date |
| Q1'20 | 3/31/2020 |
| Q2'20 | 6/30/2020 |
| Q3'20 | 9/30/2020 |
| Q4'20 | 12/31/2020 |
| Q1'21 | 3/31/2021 |
| Q2'21 | 6/30/2021 |
| Q3'21 | 9/30/2021 |
| Q4'21 | 12/31/2021 |
| Q1'22 | 3/31/2022 |
| Q2'22 | 6/30/2022 |
| Q3'22 | 9/30/2022 |
| Q4'22 | 12/31/2022 |
| Q1'23 | 3/31/2023 |
| Q2'23 | 5/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!
