Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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!
@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