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

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:

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:

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
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

#### 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.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors