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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
clubspec
Helper III
Helper III

Find previous dates of selected date

Hi Gurus,

I need to find the previous dates based on selected date.

I created a measure: Last Insp Date = CALCULATE(MAX('TABLE'[Date]), 'TABLE'[Date] < MAX('TABLE'[Date])) this bit works fine, when I select a date from a slicer.

 

Then I created another measure to show the 2nd Last Insp Date = CALCULATE(MAX('TABLE'[Date]), FILTER(ALL('TABLE'), 'TABLE'[Date] < [Last Insp Date])) I used the previous measure I created as reference but this returns (Blank), I expect it would return 25/09/23 as picture below.  What did I do wrong?

 

clubspec_0-1721623028969.png

Thank you

1 ACCEPTED SOLUTION

Hi @clubspec - yes, to calculate the 3rd Last Inspection Date, you would need to extend the DAX formula to account for the previous inspection dates. 

3rd Last Insp Date =
CALCULATE(
MAX('TABLE'[Date]),
FILTER(
ALL('TABLE'),
'TABLE'[Date] < [2nd Last Insp Date]
)
)

 

you can easily extend the logic to calculate the 4th, 5th, and nth Last Inspection Dates as needed by following the same pattern.

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

3 REPLIES 3
rajendraongole1
Super User
Super User

Hi @clubspec -create the first calculation as below

First Last Inspection Date:

Last Insp Date = CALCULATE(MAX('TABLE'[Date]), 'TABLE'[Date] < MAX('TABLE'[Date]))

 

Adjust your second Last Inspection Date as expected result 

2nd Last Insp Date =
CALCULATE(
MAX('TABLE'[Date]),
FILTER(
ALL('TABLE'),
'TABLE'[Date] < CALCULATE(MAX('TABLE'[Date]), 'TABLE'[Date] < MAX('TABLE'[Date]))
)
)

 

Hope it works 

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Thank you rajendraongole1,

It works!  Now if I want to calculate the 3rd Last Insp Date do I have to repeat the very long DAX to return 24/09/23?

Hi @clubspec - yes, to calculate the 3rd Last Inspection Date, you would need to extend the DAX formula to account for the previous inspection dates. 

3rd Last Insp Date =
CALCULATE(
MAX('TABLE'[Date]),
FILTER(
ALL('TABLE'),
'TABLE'[Date] < [2nd Last Insp Date]
)
)

 

you can easily extend the logic to calculate the 4th, 5th, and nth Last Inspection Dates as needed by following the same pattern.

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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