cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## sum last value of each year

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.

1 ACCEPTED SOLUTION
Solution Sage

Hi @Njoerd  if you have a date table try this

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

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
2 REPLIES 2
Solution Sage

Hi @Njoerd  if you have a date table try this

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

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors