Forum Discussion

zgoodman's avatar
zgoodman
Regular Visitor
8 years ago

Moving Average

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...
v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

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. :smileyhappy:

 

Regards

zgoodman's avatar
zgoodman
Regular Visitor
8 years ago

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's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    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. :smileyhappy:

     

     

    Regards

    • zgoodman's avatar
      zgoodman
      Regular Visitor
      8 years ago

      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's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        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. :smileyhappy:

         

        Regards