Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
luisccmm
Helper II
Helper II

Rolling SUM using SUMX

I have a data model that has 2 basic tables (Marging_Turnover  table & Master_Calendar table). I have attached a PWBI file  for reference.
Explanation
Margin_turnover table has only 3 columns, one is Yearmonth_num and the other 2 columns are:

- Margin

- Turnover
photo_model2.jpg

Expected_result using a Calculated column:

I have created a Calculated Column to show exactly the Result I am looking  called "Rolling_sum", that is calculated from column Profit_column (Profit_column basically multiplies Margin*Turnover)

 

Rolling_sum = 
CALCULATE(sum(Marging_Turnover[Profit_column]),
FILTER(ALL('Master Calendar'[Year_month_num]),'Master Calendar'[Year_month_num]<=max('Master Calendar'[Year_month_num])))

 

What I need to achieve?
Create a Measure without using Profit_colum or any other  that is the product from Marging*Turnover in a row-wise style. I am pretty sure that the way to do it might be using SUMX as it is supposed to be a row-wise function, but I am not able to get it.
NOTE: NOT Calculated Column are allowed, and you cannot use Profit_column, it is only as reference.
My attempt is called "Rolling sum with SUMX"  in Orange Color on the image Below

 

sumx(VALUES('Master Calendar'[Year_month_num]),
CALCULATE(
sum(Marging_Turnover[Marging])*sum(Marging_Turnover[Turnover]),
filter(all('Master Calendar'[Year_month_num]),'Master Calendar'[Year_month_num]<=max('Master Calendar'[Year_month_num]))))

 

photo_model.jpg
I have attached a simple PWBI file 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Rolling_sum_with_sumx = 
var m = max('Master Calendar'[Year_month_num])
var a = SUMMARIZE(ALLSELECTED('Master Calendar'),[Year_month_num],"sm",sum(Marging_Turnover[Profit_column]))
return SUMX(FILTER(a,[Year_month_num]<=m),[sm])

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

Rolling_sum_with_sumx = 
var m = max('Master Calendar'[Year_month_num])
var a = SUMMARIZE(ALLSELECTED('Master Calendar'),[Year_month_num],"sm",sum(Marging_Turnover[Profit_column]))
return SUMX(FILTER(a,[Year_month_num]<=m),[sm])

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.