Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there,
Wondering if anyone has attempted this before. I'm trying to find max date per group, second max date and then third.
I'm okay with getting max and second to that by using the following:
The calculation to find max date per group:
Solved! Go to Solution.
Hey @kasiaw29 ,
if that worked, then you can just repeat it:
3rd Latest Date =
CALCULATE(
MAX( 'Table'[Date] ),
ALLEXCEPT( 'Table', 'Table'[ID] ),
'Table'[Date] <> [Latest Date] && 'Table'[Date] <> [2nd Latest Date]
)
@kasiaw29 I'm thinking that you should do something like:
Measure =
VAR __Table = ADDCOLUMNS('Query1',"__Rank",RANKX('Query1',[Date],,DESC))
RETURN
MAXX(FILTER(__Table,[__Rank] = 3),[Date])
Hey @kasiaw29 ,
if that worked, then you can just repeat it:
3rd Latest Date =
CALCULATE(
MAX( 'Table'[Date] ),
ALLEXCEPT( 'Table', 'Table'[ID] ),
'Table'[Date] <> [Latest Date] && 'Table'[Date] <> [2nd Latest Date]
)
Should have thought of that! Magic, worked perfectly!