DAX help to calculate current year and previous year score bases on the max date of each project id.

[Anonymous]

Hello experts,
I want to create a calculated column for current year and previous year score bases on the max date of each project id.

Please help

Input Table

Project ID	Date	Score	Category
1	01-01-2024	15	A
1	05-05-2024	5	B
1	07-07-2023	100	A
1	09-09-2023	30	A
2	01-01-2024	150	A
2	02-02-2023		A
3	01-01-2024	70	A
3	02-02-2023	90	B

Output Table

Project ID	Category	Current Year Max Score	Previous Year Max Score
1	A	15	30
1	B	5
2	B	150
3	A	70
3	B		90

[Anonymous]

Thank you @Anonymous for the quick help.
But below formula is giving wrong output, getting current year score in previous year formula.
Also I need to consider category also in the formula to identify unique records.
Please help @Anonymous 

Previous Year Max Score =

VAR _maxDate = CALCULATE(MAX(Table[Date]),

FILTER(ALLEXCEPT('Table', 'Table'[ID],Table[Category]),
YEAR(Table[Date]) = YEAR(TODAY()) - 1))

RETURN

CALCULATE(MAX(Table[Score]), FILTER('Table', Table[Date] = _maxDate))

[Anonymous · Accepted solution]

Hi @Anonymous 

You can modify the formula to the following:

Previous Year Max Score = 
VAR _maxDate = CALCULATE(MAX([Date]), FILTER(ALLEXCEPT('Table', 'Table'[Project ID], 'Table'[Category]), YEAR([Date]) = YEAR(TODAY()) - 1))
RETURN
CALCULATE(MAX([Score]), FILTER(ALLEXCEPT('Table', 'Table'[Project ID], 'Table'[Category]), [Date] = _maxDate))

Current Year Max Score = 
VAR _maxDate = CALCULATE(MAX([Date]), FILTER(ALLEXCEPT('Table', 'Table'[Project ID], 'Table'[Category]), YEAR([Date]) = YEAR(TODAY())))
RETURN
CALCULATE(MAX([Score]), FILTER(ALLEXCEPT('Table', 'Table'[Project ID], 'Table'[Category]), [Date] = _maxDate))

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous , can we do the same calculation based on the selected year?

[Anonymous]

Thank you @Anonymous for the quick help

[Anonymous]

Hi @Anonymous 

You can create two calculated columns.

Current Year Max Score = 
VAR _maxDate = CALCULATE(MAX([Date]), FILTER(ALLEXCEPT('Table', 'Table'[Project ID]), YEAR([Date]) = YEAR(TODAY())))
RETURN
CALCULATE(MAX([Score]), FILTER('Table', [Date] = _maxDate))

Previous Year Max Score = 
VAR _maxDate = CALCULATE(MAX([Date]), FILTER(ALLEXCEPT('Table', 'Table'[Project ID]), YEAR([Date]) = YEAR(TODAY()) - 1))
RETURN
CALCULATE(MAX([Score]), FILTER('Table', [Date] = _maxDate))

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.