Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
This seems to be the best way to do it. Thank you!
pls try this
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |