sum last value of each year

Njoerd — Wed, 28 Sep 2022 12:54:29 GMT

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.

Re: sum last value of each year

DimaMD — Wed, 28 Sep 2022 13:45:21 GMT

Hi Njoerd if you have a date table try this

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

Re: sum last value of each year

Njoerd — Wed, 28 Sep 2022 13:56:28 GMT

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

topic Re: sum last value of each year in DAX Commands and Tips

sum last value of each year

Re: sum last value of each year

Re: sum last value of each year