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

  • zgoodman's avatar
    zgoodman
    Regular Visitor
    8 years ago

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