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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Almantas32
Helper II
Helper II

Monthly rolling average

Hey guys, 

 

I wanted to calculate the monthly rolling average of completed tasks, using the same formula I used for weekly average. But something is off and it does not work for moths in the same way. 

I am Attaching a link to file as an example:

https://drive.google.com/file/d/1TNMMGjRvBQi9M65z1UkKt2vmCk_dikgI/view?usp=sharing

 

Should i modify the formula or go with a different one for moths completely? 

 

 

1 ACCEPTED SOLUTION

@Almantas32 ,

Try like

Tasks Monthly Average = CALCULATE(AVERAGEX(VALUES('Calendar Tasks'[Month Name]), [Total Tasks Done]),FILTER(ALLSELECTED('Calendar Tasks'), 'Calendar Tasks'[Month Name] <= MAX('Calendar Tasks'[Month Name])))

 

Some changes will be need in case you get additional data

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Almantas32 , I checked the file. what is expected output?

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hey, 

 

the problem is the rolling average (yellow line) in upper chart should show a rolling average all months + current. Instead it shows the sum off tasks that month. 

I use the same formula for weeks in the chart below and it works. 

 

Almantas32_0-1600344131271.png

 

@Almantas32 ,

Try like

Tasks Monthly Average = CALCULATE(AVERAGEX(VALUES('Calendar Tasks'[Month Name]), [Total Tasks Done]),FILTER(ALLSELECTED('Calendar Tasks'), 'Calendar Tasks'[Month Name] <= MAX('Calendar Tasks'[Month Name])))

 

Some changes will be need in case you get additional data

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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