Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
79 | |
67 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |