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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.