Moving Average Calculation not working

DAX DAX Calculations
1

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.

image
image1664Ă—744 194 KB

Thanks
Radhika

2

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”.
image

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.

image

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

Kind Regards,

3

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

4

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

image
image1226Ă—437 23.9 KB

Kind Regards,
Michael

1 Like
5

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

6

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

image
image2202Ă—1323 279 KB

2 Likes
7

It’s great to know that you are making progress with your query @Radz. :slight_smile:

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!

8

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