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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

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

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

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors