Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Njoerd
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:

DateIDVALUE
01.03.2022100900
01.06.20221001200
31.12.2023100800
01.03.2022200500
01.06.2022200600
31.12.2023200500

 

I would like to achieve the following.

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

IDTotal Value
1002000
2001100

 

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:

ID20222023Total
1001200800800
200600500500

 

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
DimaMD
Solution Sage
Solution Sage

Hi @Njoerd  if you have a date table try this

 

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

Screenshot_4.jpg

 

 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

View solution in original post

2 REPLIES 2
DimaMD
Solution Sage
Solution Sage

Hi @Njoerd  if you have a date table try this

 

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

Screenshot_4.jpg

 

 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

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

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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