Rolling average for last 3 custom months

[karthikeyan9395]

Hi all,

I am struck up with calculating the rolling average for previous 3 custom months. Here we follow 13 month Fiscal calendar. So it is hard for me to achieve the moving average concept in Power BI measure.

This is my aggregarated table 'Sheet1'.

Expected output needs to be like this in a table visual. 

For month 3 it is calculated as follows: (32.48+52.99+91.94)/3 = 59.14

And for month 4 it is calculated as: (52.99+91.94+75.95)/3 = 73.63

Can anyone help me to solve this? 

[karthikeyan9395 · Accepted solution]

Thanks @amitchandak. I developed the solution to the above query myself.

Here's the measure I developed without using any dates in the moving average concept.

moving_average_3months =

DIVIDE(IF(max(Sheet1[yearmonthorder]) > 2,

CALCULATE(sum(Sheet1[sales]),

FILTER(ALLSELECTED(Sheet1[yearmonthorder]),

Sheet1[yearmonthorder] <= max(Sheet1[yearmonthorder]) &&

Sheet1[yearmonthorder] >= max(Sheet1[yearmonthorder]) - 2)

),

BLANK()),3,BLANK())

[amitchandak]

@karthikeyan9395 , Try like with a date table

Rolling 3 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-12,MONTH)) ,
CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH), not(isblank((Sales[Sales])))))

Or

if( CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH), not(isblank((Sales[Sales])))) < 3, blank() ,

Rolling 3 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-12,MONTH)) ,
CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH), not(isblank((Sales[Sales]))))) )

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[karthikeyan9395]

Hi @amitchandak ,

Thanks for your quick response. I am not able to use the dates in my measure because the dates are not constant for all the JCs sometimes it would be 28days in a month or 29days in a month. Hence I have created a custom column "yearmonthorder" which I used in measure to negate the 2 months. 

Here's the measure I used and it was not working.

Measure =

var acquistionmonth = SELECTEDVALUE(Sheet1[yearmonthorder])

var temp3 = AVERAGEX(CALCULATETABLE(Sheet1,FILTER(Sheet1,Sheet1[yearmonthorder] > acquistionmonth - 2)),Sheet1[sales])

return temp3

I have attached a Sample PBI-Moving Average.pbix  file for your reference. 

And thanks for helping me on this.