Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zerosugar
Helper II
Helper II

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!

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

2 REPLIES 2
zerosugar
Helper II
Helper II

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

Ahmedx
Super User
Super User

pls try this

Screen Capture #1232.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.