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
dev85
Frequent Visitor

Cumulative sum by period and other column

Hi all!

 

I´m need to create a table with a column that calculates the cumulative sum based on period and country. Right now I have this:

dev85_0-1682692977067.png

using this measure for the cumulative:

cummulative cost = 
CALCULATE(SUM('tablename'[new cost savings]),
FILTER(ALL('tablename'),
'tablename'[finishdate] <= MAX('tablename'[finishdate])
))

Without any more data in the table all good, but when I add the country column the values just repeats, like this:

dev85_1-1682693279692.png

Is there a way to calculate the cumulative by the period (month) and if I display the country to add each country cost? Just to clarify, I don't need to calculalte the cumulative sum by country (grouping) just that if I display the countries then to add each country value in a cumulative way.

 

Thanks in advance for any help!

1 REPLY 1
amitchandak
Super User
Super User

@dev85 , You can join the date of your table with a date of date table and try a formula like, use date/period from date table in visual

 

cummulative cost =
CALCULATE(SUM('tablename'[new cost savings]),
FILTER(ALL('Date'),
'Date'[Date] <= MAX('Date'[Date])
))

 

Example Date table

= Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "FY Year", if( Month(_max) <7 , year(_max)-1 ,year(_max))
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 

You can also consider window function

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 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.

Top Solution Authors