Rolling Avg

[nknishant]

I want to know how we write the Dax code in Rolling of average sales without use of averagex function in easiest way.

can you please help here.

@amitchandak 

[amitchandak · Accepted solution]

@nknishant , if you need row level avg 

Rolling 12 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

if not 

Rolling 12 Avg = CALCULATE(Divide( sum(Sales[Sales Amount]) , distinctcount('Date'[Month Year]) ) ,DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

or

Rolling 12 Avg = CALCULATE(Divide( sum(Sales[Sales Amount]) , countrows(sumamrize(Sales,'Date'[Month Year]) ) ,DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

[nknishant]

Thank you for the reply soon.👍

[Ashish_Mathur · Accepted solution]

Hi,

Why do you not want to use the AVERAGEX() function?  Share some data to work with and show the expected result.

[nknishant]

Thank you @Ashish_Mathur AverageX we can also use here for the same but as per solution of row level wise. 
so we using here calculate with expression and use Datesinperiod for the result.

[nknishant]

Thank you for the prompt reply. I will follow this dax method. 

[amitchandak · Accepted solution]

@nknishant , if you need row level avg 

Rolling 12 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

if not 

Rolling 12 Avg = CALCULATE(Divide( sum(Sales[Sales Amount]) , distinctcount('Date'[Month Year]) ) ,DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

or

Rolling 12 Avg = CALCULATE(Divide( sum(Sales[Sales Amount]) , countrows(sumamrize(Sales,'Date'[Month Year]) ) ,DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))