sum last value of each year

[Njoerd]

Hej,

i can't figure out how to solve this problem. I have data similiar to this:

Date	ID	VALUE
01.03.2022	100	900
01.06.2022	100	1200
31.12.2023	100	800
01.03.2022	200	500
01.06.2022	200	600
31.12.2023	200	500

I would like to achieve the following.

I need the totals of the latest value of each year per category summed up.

ID	Total Value
100	2000
200	1100

I am able to calculate the values and place them in a matrix, which shows the outcome i need per year:

LastPlanValueYear = 
var LastPlanDate = MAXX(TABLE, TABLE[Date])

return
CALCULATE(
    SUM(TABLE[Value]),
    Filter('TABLE', TABLE[Date] = LastPlanDate)
)

Matrix:

ID	2022	2023	Total
100	1200	800	800
200	600	500	500

I can't figure out how to get the totals right, and i need them as calculated measure for each ID. The matrix is just for demonstration purposes. Thanks for your help.

[DimaMD · Accepted solution]

Hi @Njoerd  if you have a date table try this

Total = 
IF( HASONEVALUE(Dates[Year]),
[LastPlanValueYear],
SUMX( VALUES('Dates'[Year]),
[LastPlanValueYear]))

[Njoerd]

Great!
I get what this code does but would have never figured this out by myself. Thanks a lot!