Forum Discussion
Solved
Calculated column to flag max count per category and dates per category after the date associated
hello - i am looking to create a calculated column that flags the max count per title and any date after the max count per title date in the same title group. I cannot figure how to do this.
Title Count AssignmentDate
| Course 2 | 16 | 11/01/2024 |
| Course 2 | 1 | 12/01/2024 |
| Course 4 | 1 | 11/12/2023 |
| Course 4 | 12 | 29/01/2024 |
| Course 4 | 4 | 24/01/2024 |
| Course 4 | 2 | 10/11/2023 |
| Course 4 | 1 | 23/01/2024 |
| Course 4 | 1 | 12/01/2024 |
| Course 4 | 1 | 12/12/2023 |
| Course 4 | 3 | 19/01/2024 |
| Course 4 | 1 | 03/01/2024 |
| Course 5 | 1 | 07/09/2023 |
| Course 7 | 1 | 24/01/2024 |
| Course 7 | 3 | 19/01/2024 |
| Course 7 | 1 | 12/01/2024 |
| Course 7 | 1 | 17/01/2024 |
| Course 7 | 1 | 03/01/2024 |
| Course 6 | 1 | 19/10/2023 |
| Course 1 | 1 | 14/12/2023 |
| Course 3 | 12 | 25/01/2024 |
and this is what i woudl like to see -
thank you for your help!
Hi,
Please use this formula:"
VAR __MaxCount =CALCULATE(MAX(T_CalColumn[Count]),ALLEXCEPT(T_CalColumn,T_CalColumn[Title]))VAR __DateMaxCount =CALCULATE(MAX(T_CalColumn[AssignmentDate]),FILTER(ALLEXCEPT(T_CalColumn,T_CalColumn[Title]),T_CalColumn[Count]=__MaxCount))VAR __Result =IF([AssignmentDate]>__DateMaxCount || [Count]=__MaxCount,"Y")RETURN__ResultFinal result should be this:
1 Reply
_AAndradeResident Rockstar
Hi,
Please use this formula:"
VAR __MaxCount =CALCULATE(MAX(T_CalColumn[Count]),ALLEXCEPT(T_CalColumn,T_CalColumn[Title]))VAR __DateMaxCount =CALCULATE(MAX(T_CalColumn[AssignmentDate]),FILTER(ALLEXCEPT(T_CalColumn,T_CalColumn[Title]),T_CalColumn[Count]=__MaxCount))VAR __Result =IF([AssignmentDate]>__DateMaxCount || [Count]=__MaxCount,"Y")RETURN__ResultFinal result should be this: