March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet 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
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
Solved! Go to 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!!
Proud to be a 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!!
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!!
Proud to be a Super User! | |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
99 | |
90 | |
72 | |
62 |
User | Count |
---|---|
140 | |
121 | |
106 | |
98 | |
94 |