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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JWick1969
Helper IV
Helper IV

Solved. Calculate average of a measure to get the previous 3 month average

Hi,

 

I am trying to build a DAX measure to calculate previous 3 month average of a measure. right now i have a data that start from Jan 2020 to Aug 2020. Currently i have this two DAX code but it gives me different result. 

 

1. prev 3 Months average = CALCULATE(AVERAGEX('Table','Table'[rec %]), ALL(DateTable),
DATESBETWEEN(DateTable[Date],DATEADD(LASTDATE(DateTable[Date]),-4,MONTH),DATEADD(LASTDATE(DateTable[Date]),-1,MONTH)))


2. prev 3 Months average =


CALCULATE(
AVERAGEX('Table','Table'[rec %]),
FILTER(
ALL(DateTable),
DateTable[MonthNumber] > MAX(DateTable[MonthNumber] ) - 4
&& DateTable[MonthNumber] <= MAX(DateTable[MonthNumber] )-1
) , VALUES(DateTable[Year])
)

Expected result:
Untitled.jpg
 
Appreciate the help.
Thank you.
1 ACCEPTED SOLUTION

Hi @Greg_Deckler @amitchandak ,  Thank you for your time.

I already resolved the issue. I need only to sort the date in Visualization.

Average rec% of previous 3 months = if(ISBLANK([Rec%]),BLANK(),IF(COUNTROWS(DATESBETWEEN(DateTable[Date],EDATE(MIN(DateTable[Date]),-3),MIN(DateTable[Date])-1))>61,CALCULATE(AVERAGEX(values(DateTable[Date]),[Rec%]),DATESBETWEEN(DateTable[Date],EDATE(MIN(DateTable[Date]),-3),MIN(DateTable[Date])-1)),BLANK()))
 
Untitled.jpg

View solution in original post

8 REPLIES 8
Greg_Deckler
Community Champion
Community Champion

@JWick1969 This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Hi Greg,

 

Good day! 

I'm using the below code in my actiual PBI to get the average of previous 3 months and i'm getting incorrect values. May i know what is the problem with my code. Thank you.

 

AvgPrv3Months =
var avprev3mos = if(ISBLANK([FG %]),BLANK(),IF(COUNTROWS(DATESBETWEEN(DateTable[Date],EDATE(MIN(DateTable[Date]),-3),MIN(DateTable[Date])-1))>61,CALCULATE(AVERAGEX(values(DateTable[Date]),[FG %]),DATESBETWEEN(DateTable[Date],EDATE(MIN(DateTable[Date]),-3),MIN(DateTable[Date])-1)),BLANK()))
return avprev3mos

 

Result:

Sample2.jpg

 

 

 

amitchandak
Super User
Super User

@JWick1969 , Try like Assumes [rec %] is a measure

Rolling 3 till last 1 month = CALCULATE([rec %],DATESINPERIOD('DateTable'[Date],ENDOFMONTH(dateadd(DateTable[Date1,month)),-3,MONTH))

 

In case you want to use AVERAGEX('Table','Table'[rec %]) the use like

AVERAGEX(values('DateTable'[Month-year]),[rec %])

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi @amitchandak , Thank you for the reply. Tried the code and the result is replicated the value of rec% and not getting the average of previous 3 months. below is the DAX Measure

previous 3 months = CALCULATE([rec %], DATESINPERIOD(DateTable[Date], LASTDATE(DATEADD(DateTable[Date], -1,MONTH)), -3,MONTH))


Untitled.jpg

@JWick1969 , Month and year in the visual are coming from the date table and the date table is marked as a date table, right click on the table there is an option

also try

previous 3 months = CALCULATE([rec %], DATESINPERIOD(DateTable[Date], LASTDATE(DATEADD(DateTable[Date], -1,MONTH)), -3,MONTH), all(Date))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi @amitchandak , Already mark DateTable as Date Table and still i'm getting different result. Please see below actual result using two different DAX Code.  

 

Prev 3 Mos =
CALCULATE(MeasureTable[Rec%], DATESINPERIOD(DateTable[Date], LASTDATE(DATEADD(DateTable[Date], -1,MONTH)), -3,MONTH), ALL(DateTable[Date]))
 
3MosPrev =
CALCULATE(AVERAGEX(values(DateTable[Date]),MeasureTable[Rec%]),DATESINPERIOD('DateTable'[Date],ENDOFMONTH(DATEADD(DateTable[Date],-1,MONTH)),-3,MONTH))



Untitled.jpg

 

@JWick1969 - Can you post sample source data in your table as text that replicates this issue? Way easier to test and get to a resolution. You may also find these helpful, you can just create the filters yourself using like EOMONTH, etc.

 

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler @amitchandak ,  Thank you for your time.

I already resolved the issue. I need only to sort the date in Visualization.

Average rec% of previous 3 months = if(ISBLANK([Rec%]),BLANK(),IF(COUNTROWS(DATESBETWEEN(DateTable[Date],EDATE(MIN(DateTable[Date]),-3),MIN(DateTable[Date])-1))>61,CALCULATE(AVERAGEX(values(DateTable[Date]),[Rec%]),DATESBETWEEN(DateTable[Date],EDATE(MIN(DateTable[Date]),-3),MIN(DateTable[Date])-1)),BLANK()))
 
Untitled.jpg

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.