cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
michael_knight
Post Prodigy
Post Prodigy

3 Month Average, excluding current month

Hi,

 

I'm trying to create a measure which will give me an average of the previous 3 months, but exclude the current month. For example, for May 2021 I want that to be an average of February 2021, March 2021 and April 2021. 

 

Currently using the measure below I have been able to display the 3 month average but for May 2021 it's the Average of March, April and May

3 Month Rolling Average Fall Through = 
IF (
ISBLANK ( [Withdrawals]),
BLANK (),
CALCULATE (
AVERAGEX ( VALUES ( 'Date'[Month/Year] ),[Withdrawal %]),
DATESINPERIOD ( 'Date'[Full Date], LASTDATE ( 'Date'[Full Date] ), -3, MONTH )
)
)

 

 

This is what the average is on a month to month basis

Withdrawal.PNG

 

This is what it looks like on a 3 Month Average basis

Withdrawal 3 month.PNG

 

Looking at the first Visual, the average for March, April and May is 38%, 47% and 24% which gives us an Average over them 3 months of 36%. 

 

The figure that I want is the average for February, March and April which is 50%, 38% and 47% which gives us an average of 45%. 45% is what should be showing in the May 2021 3 month average

 

Does anyone know how I can do this?

 

I'll attach the PBIX file below

https://www.dropbox.com/s/xkoqrr1n7zabshn/Withdrawal%20Help.pbix?dl=0

 

Cheers,

Mike

1 ACCEPTED SOLUTION

@michael_knight , sorry, my mistake. Try like

 

CALCULATE (
AVERAGEX ( VALUES ( 'Date'[Month/Year] ),[Withdrawal %]),
DATESINPERIOD ( 'Date'[Full Date], eomonth ( max('Date'[Full Date]) ,-1), -3, MONTH )
)



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@michael_knight , Try  like

 

3 Month Rolling Average Fall Through =
IF (
ISBLANK ( [Withdrawals]),
BLANK (),
CALCULATE (
AVERAGEX ( VALUES ( 'Date'[Month/Year] ),[Withdrawal %]),
DATESINPERIOD ( 'Date'[Full Date], eomonth ( 'Date'[Full Date] ,-1), -3, MONTH )
)
)

 

 

or only

 

CALCULATE (
AVERAGEX ( VALUES ( 'Date'[Month/Year] ),[Withdrawal %]),
DATESINPERIOD ( 'Date'[Full Date], eomonth ( 'Date'[Full Date] ,-1), -3, MONTH )
)

 

make sure Date is marked as date table



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Hi @amitchandak 

 

I tried both suggestions and got this error

 

"A single value for column 'Full Date' in table 'Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

 

I made sure to mark Date as a date table too 

@michael_knight , sorry, my mistake. Try like

 

CALCULATE (
AVERAGEX ( VALUES ( 'Date'[Month/Year] ),[Withdrawal %]),
DATESINPERIOD ( 'Date'[Full Date], eomonth ( max('Date'[Full Date]) ,-1), -3, MONTH )
)



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Legend, thank you @amitchandak 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors