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

 

Thank you

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

3 Replies

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