Forum Discussion

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

Need Help: Rolling Average (Last 2 days) | When multiple dates in a column

I have an original table using which I need to calculate the rolling average of rating table. However I do not get the expected output. Can somebody please help me with a DAX solution for this ?  Pl...
Need Help
Show and Tell
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi ketan10

     

    Try this MEASURE

     

    Rolling 2 Days Average =
AVERAGEX (
    TOPN (
        2,
        FILTER (
            ALL ( TableName[Date] ),
            TableName[Date] <= SELECTEDVALUE ( TableName[Date] )
        ),
        TableName[Date], DESC
    ),
    CALCULATE ( AVERAGE ( TableName[Rating] ) )
)
rabih's avatar
rabih
Frequent Visitor
8 years ago

I replicated your data and chose Average of Rating instead of Sum or Don't summarize etc..

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

Hi ketan10

 

Try this MEASURE

 

Rolling 2 Days Average =
AVERAGEX (
    TOPN (
        2,
        FILTER (
            ALL ( TableName[Date] ),
            TableName[Date] <= SELECTEDVALUE ( TableName[Date] )
        ),
        TableName[Date], DESC
    ),
    CALCULATE ( AVERAGE ( TableName[Rating] ) )
)