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