Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JMAlloway
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.

 

JMAlloway_0-1612462018224.png

 

 

Thanks for any info on this issue!

 

 

 

2 REPLIES 2
v-jingzhang
Community Support
Community Support

@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]))

020903.jpg

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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