Moving Average

[zgoodman]

Hello, I am able to get a moving average using this formula:

^cpc_3_week = CALCULATE (
    AVERAGEX ( 'Query1', Query1[^cpc] ),
    DATESINPERIOD (
        Query1[isodate],
        LASTDATE ( Query1[isodate] ),
        -21,
        day
    )
)

As you can see here this gives me what I am looking for:

However, when I use the same formula for other moving averages it doesn't work. The only thing I am changing is "Query1[^cpc]" and this is the result:

Anyone know what the issue might be? Thanks!

[v-ljerr-msft]

Hi @zgoodman,

Could you post your table structures with some sample/mock which can help us to reproduce the issue? So that we can help further investigate on it. It's better that you can just share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[zgoodman]

I found the issue. It turns out that the x-axis on my tables was by week and the calculation in my measure was by day. I didn't realize this was an issue but glad it is solved.

However, this presents a new problem. I need this average but I also need to be able to display it in weekly form. It appears in my measue Day, Month, Quarter, and Year are the only ways I can calculate this. Is there any way I can do it by week instead?

[v-ljerr-msft]

Hi @zgoodman,

---

However, this presents a new problem. I need this average but I also need to be able to display it in weekly form. It appears in my measue Day, Month, Quarter, and Year are the only ways I can calculate this. Is there any way I can do it by week instead?

---

In this scenario, you can firstly create a custom hierarchy with your Year, Quarter, Month, WeekNum, Date column, then you should be able to use this new created hierarchy to get your expected result. 

Regards

[zgoodman]

This looks great! Is there a way for me to label the week as the date at the beginnign of the week instead of the week number?

[v-ljerr-msft]

Hi @zgoodman,

---

Yes, there is. You should be able to use the formula below to create a new calculate column in your Date table.

FirstDayOfWeek = CALCULATE(FIRSTDATE('Date'[Date]),ALLEXCEPT('Date','Date'[WeekNo]))

Then you can use the 'FirstDayOfWeek' column to create the custom hierarchy. 

Regards

[zgoodman]

It appears that becasue I am calculating the average using days instead of weeks I am still having the original problem of the graph looking funky when I display it in week form. Is there a way to do the actual calculation as 3 weeks instead of 21 days. Again, here is the formula:

^cpc_3_week = CALCULATE (
    AVERAGEX ( 'Query1', Query1[^cpc] ),
    DATESINPERIOD (
        Query1[isodate],
        LASTDATE ( Query1[isodate] ),
        -21,
        day
    )
)