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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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