Help with Cumulative Average in Date Based Matrix

[JMAlloway]

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!

[v-jingzhang]

@JMAlloway 

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.

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

[amitchandak]

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

example

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