# Moving Average Calculation not working

Hi Everyone,

Need help to understand where my logic went wrong. I am going through the Dax patterns course and in it I am trying to calculate the 14 days moving average through different formulas.

1. Shown by Sam in the course -
Moving Average 14 Days (course) =
AVERAGEX(
DATESINPERIOD( Dates[Date], LASTDATE( Dates[Date] ), -14, DAY ),
[Total Sales] )

2. Using Averagex function
Moving Average 14 Days (Averagex) =
AVERAGEX(
FILTER( ALL(Dates),
Dates[Date] > (MAX( Dates[Date] ) - 14) &&
Dates[Date] <= MAX( Dates[Date] ) ), [Total Sales] )

3. Using Calculate function
Moving Average 14 Days (Calculate) =
CALCULATE ( AVERAGEX ( Sales, Sales[Total Revenue] ),
FILTER ( ALL ( Dates[Date] ),
Dates[Date] > ( MAX ( Dates[Date] ) - 14 ) &&
Dates[Date] <= MAX( Dates[Date] ) ) )

Dax course.pbix (438.0 KB)
When I am using Samâ€™s formula or Averagex formula then it is giving me same results which are correct. But when I am using Calculate formula then it is not giving the same result. Can someone please explain why the calculate formula is not working and what needs to be done to get a correct result using this formula.

Thanks
Radhika

Hi @Radz ,

These 2 are calculating different things and that is why throwing different results:

Below formula calculates moving average of â€śTotal Salesâ€ť in last 14 days. So, it calculates total sales in last 14 days and divide by 14. So, it is moving average â€śDaily Saleâ€ť.

However, below formula brings average transaction sale of last 14 days. So, first one is moving average of daily sale and below one in average transaction sale in last 14 days.

I am not good at explaining concepts, but I hope that it will guide you somehow.

Kind Regards,

HI Michael,

Thanks for the explanation. I kind of get what you are saying but still not able to figure out how I need to change my calculate formula to get moving average. Can you please guide me on how this can be done?

Thanks
Radhika

Hi @Radz ,

Great that you understood. There can be many different ways but you can get same result. You can use below combination with calculate:

``````Moving Average 14 Days (calculate) =
CALCULATE (
AVERAGEX ( VALUES ( 'Dates'[Date] ), [Total Sales] ),
DATESINPERIOD ( 'Dates'[Date], LASTDATE ( 'Dates'[Date] ), -14, DAY )
)
``````

Kind Regards,
Michael

1 Like

Thanks for that, appreciate your responses. This looks to be similar to Samâ€™s measure. What I am actually looking for is how can I achieve this using Calculate and Filter instead of using Averagex and Datesinperiod. I know that Samâ€™s way is the easiest one but its just that I am trying things out just so I get a deeper understanding of Dax. My formula using calculate and filter is actually not a good example but still want to see how it will workâ€¦

Thanks again for your help.

Thanks
Radhika

Hi @Radz ,Radhika,

Thank you very much for posting an interesting question . .

Please find below the DAX Query Average using only CALCULATE as you desire in the question.

## Moving Average 14 Days (calculate_Dhrub) = CALCULATE ( AVERAGEX ( Dates, CALCULATE ( SUM ( Sales[Total Revenue] ) ) ), FILTER ( ALL ( Dates ), Dates[Date] > ( MAX ( Dates[Date] ) - 14 ) && Dates[Date] <= MAX ( Dates[Date] ) ) )

Please find screenshot below , Please let me know if it helps.

I accept kudos as a token of motivation for my work (In case I could )

2 Likes

Itâ€™s great to know that you are making progress with your query @Radz.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey, We hope youâ€™ll give your insights on how we can further improve the Support forum. Thanks!

Hi @Dhrubojit_Goswami Apologies for my late rely. Thats perfect, this is what I was looking for. Thanks again for your help.

Thanks
Radhika

1 Like