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

[JWick1969]

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:

Appreciate the help.

Thank you.

[Greg_Deckler]

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

[JWick1969]

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:

[amitchandak]

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

[JWick1969]

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

[amitchandak]

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

[JWick1969]

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

[Greg_Deckler]

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

[JWick1969 · 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()))