topic Re: DAX help to calculate current year and previous year score bases on the max date of each project id. in DAX Commands and Tips

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

Anonymous — Wed, 19 Jun 2024 05:32:50 GMT

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

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

Anonymous — Wed, 19 Jun 2024 01:35:35 GMT

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.

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

Anonymous — Wed, 19 Jun 2024 05:38:30 GMT

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))

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

Anonymous — Thu, 20 Jun 2024 08:46:22 GMT

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.

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

Anonymous — Mon, 24 Jun 2024 09:25:46 GMT

Thank you Anonymous for the quick help

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

Anonymous — Mon, 24 Jun 2024 11:39:05 GMT

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