Hello community! I'm trying to perform a cumulative summation in PowerBi, but, I still can't solve it. Basically, I need to show my data as in the image attached. The calculation to be carried out is as follows: by columns the sum of the corresponding month must be accumulated, if there is no initial data it must show 0 and if there is no data in the following months it must continue to show the data of the last sum. Thanks for your support!

Hi, Try this Create a Calendar Table. Write the following calculated column formulas to extract the Year and Month Number. Year = Year(Calendar[Date]) and Month = Month(Calendar[Date]) Create a relationship (Many to One, Single) from the Date column of your base data table to the Date column of your Calendar Table To your matrix visual, drag Month to the row labels and Year to the column labels Write this measure to compute yearly sales. Sales = SUM(Data[Sale]) Write this measure to compute Cumulative sales. Cumulative sales = CALCULATE([Sales],DATESYTD(Calendar[Date],"31/12")) Hope this helps.

Cumulative sum for month | Microsoft Fabric Community

2 Replies

amitchandak

Super User

6 years ago

This does not seem like a raw table. But you can try like

cumm 2020 =
calculate(sum([2020]),filter(all(table),table[Mes]<=earlier(table[Mes])))

But if you have table with dates and date join with time dimension (you can create month start date for month level data ) then you can use datesmtd and datesytd (2020, cumm 2020)

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH)) 

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-12,MONTH))  
3 month back MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,MONTH)))
 


QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))





YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Year))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))

Cumm Sales = CALCULATE(SUM('Fact table'[Sales]),filter(date,date[date] <=maxx(date,date[date]) 
	&& weekday(sales[date]) <=weekday(maxx(date,date[date])) ))


Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-2,Year)),"12/31"))

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
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

date = date(year(table(year), month(table(month),1) //Date in case only have month and year

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

Ashish_Mathur
Super User
6 years ago
Hi,

Try this

Create a Calendar Table. Write the following calculated column formulas to extract the Year and Month Number. Year = Year(Calendar[Date]) and Month = Month(Calendar[Date])

Create a relationship (Many to One, Single) from the Date column of your base data table to the Date column of your Calendar Table

To your matrix visual, drag Month to the row labels and Year to the column labels

Write this measure to compute yearly sales. Sales = SUM(Data[Sale])

Write this measure to compute Cumulative sales. Cumulative sales = CALCULATE([Sales],DATESYTD(Calendar[Date],"31/12"))

Hope this helps.

Forum Discussion

Cumulative sum for month

2 Replies

Related content

Cumulative sum less than selected month

cumulative sum

Cumulative SUM

Cumulative sum of past months

cumulative sum of the past months