Forum Discussion

Jodallen123's avatar
Jodallen123
Icon for Helper I rankHelper I
1 year ago
Solved

Rolling average with blanks present

Hi,    I am trying to create a 4 week rolling average measure.    Below is what I have so far and it works on my sales data, but for some reason I can't seem to make it work on my budget data (mo...
  • sjoerdvn's avatar
    sjoerdvn
    1 year ago

    Update:
    I ran a test with the sample data you provided, and your 10 days rolling average measure actually provides the right values. Or do you want a different beviour, for example exclude non working days? .
    Anyway, there is no need to use AVERAGEX here, so for performance I would use code like below.

    4davg_ratioBU= 
VAR MaxVisibleDate = MAX('Master Time Table'[Date])
VAR RollingWindow =
    DATESINPERIOD('Master Time Table'[Date], MaxVisibleDate, -10, DAY)
RETURN
    DIVIDE(CALCULATE([Budget], RollingWindow), COUNTROWS(RollingWindow))

     

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

Jodallen123 , Try using

4davg_ratioBU =
VAR MaxVisibleDate = MAX('Master Time Table'[Date])
VAR RollingWindow =
FILTER(
'Master Time Table',
'Master Time Table'[Date] <= MaxVisibleDate &&
'Master Time Table'[Date] > MaxVisibleDate - 10
)
RETURN
AVERAGEX(
RollingWindow,
COALESCE([Budget], 0)
)

Jodallen123's avatar
Jodallen123
Icon for Helper I rankHelper I
1 year ago

Thanks for the reply! Unfortunately that does not give me the correct values, I get the result (Forum) below: