cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JWick1969
Helper III
Helper III

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

@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.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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



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



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 , 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...


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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
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 BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

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