cancel
Showing results for
Did you mean:
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),

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:

Appreciate the help.
Thank you.
1 ACCEPTED SOLUTION
Helper III

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

8 REPLIES 8
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
Helper III

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:

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 !!
Helper III

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

Super User

@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 !!
Helper III

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 =

Super User

@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
Helper III

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

Announcements

#### The Power BI Community Show

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

#### Ted's Dev Camp - July 28, 2022

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

#### 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