Forum Discussion

ketan10's avatar
ketan10
Icon for Advocate III rankAdvocate III
8 years ago
Solved

Need Help ! Moving Average over last 2 days

Hi Zubair_Muhammad I need some help with moving average formula in DAX. I tried with default rolling average but it does not satisfy my business case. Would appreciate if you could help. Here is t...
Need Help
Show and Tell
Tutorial Requests
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

ketan10

 

Try this MEASURE

 

Rolling 2 Days Average =
AVERAGEX (
    CALCULATETABLE (
        TableName,
        DATESINPERIOD ( TableName[Date], SELECTEDVALUE ( TableName[Date] ), -2, DAY )
    ),
    CALCULATE ( AVERAGE ( TableName[Rating] ) )
)
  • ketan10's avatar
    ketan10
    Icon for Advocate III rankAdvocate III
    8 years ago

    Hi Zubair_Muhammad

    Your solution works well. but partially in my case. what if I have no dates in between as shown in the image below, but i still need to plot them on a bar graph. 
    Ex: Instead of last 2 rolling days I need to plot last -30 days (w.r.t to today) so my most recent date on bar graph would 29-01-2018 then 28-01-2019, 27-01-2018 and so on


    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      ketan10

       

      Try this one

       

      Rolling Avg =
VAR mytable =
    TOPN (
        2,
        FILTER (
            ALL ( TableName[Date] ),
            TableName[Date] <= SELECTEDVALUE ( TableName[Date] )
        ),
        TableName[Date], DESC
    )
RETURN
    DIVIDE (
        CALCULATE ( SUM ( TableName[Rating] ), mytable ),
        CALCULATE ( COUNT ( TableName[Rating] ), mytable )
    )