Forum Discussion

clubspec's avatar
clubspec
Icon for Helper III rankHelper III
2 years ago
Solved

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...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    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!!

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
2 years ago

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!!

  • clubspec's avatar
    clubspec
    Icon for Helper III rankHelper III
    2 years ago

    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?

    • rajendraongole1's avatar
      rajendraongole1
      Icon for Super User rankSuper User
      2 years ago

      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!!