Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Help with Cumulative Average in Date Based Matrix

I've been struggling with finding a solution to this problem. I have a calculated column that I want to track the cumulative average by each new month this year. For example, in a matrix listed with January-December rows, I want the values to be cumulative for all previous years plus January in the 'January' row, through February in the 'February' row, etc. The image below shows the calculated column by each month, but its breaking down the calculation by its relative month for every year.  What I want is, in this case, only January to contain a value, and February-December to be blank.





Thanks for any info on this issue!




Community Support
Community Support


If you want to track the cumulative average by each new month this year, you can add a visual-level Year filter to this matrix and set value is 2021. I am not sure when you want the cumulative average, what should be the denominator to divide the cumulative total? Assume the denominator is cumulative number of months, you can create measures like below to achieve the results.

Cumulative Total = CALCULATE(SUM(Tbl[Count]),FILTER(ALL('Date'[Date]),'Date'[Date]<=MAX('Date'[Date])))
Cumulative Months = DATEDIFF(MINX(ALL('Tbl'[Date]),Tbl[Date]),MAX('Date'[Date]),MONTH)+1
Cumulative Average = IF(ISBLANK(SUM(Tbl[Count])),BLANK(),DIVIDE([Cumulative Total],[Cumulative Months]))


If you only want to show the Cumulative Average column in the matrix, click the down arrow next to Month field and check Show items with no data. Here is a PBIX file for your reference.


Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

Super User
Super User

@JMAlloway , You have to create measure like these with help from Date table (prefer)


Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(Sales),Sales[Sales Date] <=max(Sales[Sales Date])))


Only Year Cumulative


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

Helpful resources

Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors