Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |