Rolling average missing values

[Harry_Tran]

Hello everyone, I need help to calculate 7 Days Rolling Average for missing data.

I have MAIN table with Date, Users, Points, Categories, Subcategories, and Products columns. I also hace Calendar table.

My Measures:

Avg_Point_per_User_per_Day = SUM(MAIN[Points])

                                                           /

                                                           DISTINCTCOUNT(MAIN[User])

                                                           /

                                                           DISTINCTCOUNT(MAIN[Date])

7_Days_Rolling_Avarage =

                                          VAR Last_Date = LASTDATE(Calendar[Date].[Date])

                                          VAR RollingAvg =

                                                        AVERAGEX (

                                                                      DATEBETWEEN (Calendar[Date].[Date],

                                                                      DATEADD (Last_Date, -7,DAY),

                                                                      Last_Date), [Avg_Point_per_User_per_Day]

RETURN IF (Last_Date > TODAY() , BLANK(), RollingAvg)

The issue that I have is

When I apply Categories to the table, I have some data that do not have data. So, The [7_Days_Rolling_Avarage] returns (47+ 68 + 43 + 49 + 65)/5 ; (68 + 43 + 49 + 65 + 59)/5

What I expect is it would skip the missing date and return (47+ 68 + 43 + 49 + 65 + 59 + 62)/7

I came with another measure

Rolling_7_Days =

              VAR Sevendays = CALCULATETABLE ( TOPN (7,

                                                        SUMMARIZE (MAIN, MAIN [Date],

“point”, SUM (MAIN [POINTS]) / DISTINCTCOUNT (MAIN [Users])),

MAIN[Date] , DESC),

FILTER (ALL (MAIN[Date]) , MAIN[Date] <= SELECTEDVALUE (MAIN[Date])))

              RETURN

                      SUMX( Sevendays, [points]) / COUTROWS (Sevendays)

This one worked but it took almost 10 minutes to pull out the data.

Please help,

Thank you

[Icey · Accepted solution]

Hi @Harry_Tran ,

Try this:

TopN_Rolling_7days 2 =
VAR t =
    CALCULATETABLE (
        TOPN (
            7,
            SUMMARIZE (
                MAIN,
                MAIN[Category],
                MAIN[Date],
                "Avg_", [Avg_Point_per_User_per_Day]
            ),
            [Date], DESC
        ),
        FILTER ( ALLSELECTED ( MAIN[Date] ), MAIN[Date] <= MAX ( MAIN[Date] ) )
    )
RETURN
    SUMX ( t, [Avg_] ) / COUNTROWS ( t )

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[MFelix]

Believe this is duplicated with:

https://community.powerbi.com/t5/Desktop/7-days-rolling-average-for-missing-values/td-p/1539228/page...

[DataInsights]

This appears to be a duplicate of the post below:

https://community.powerbi.com/t5/Desktop/7-days-rolling-average-for-missing-values/m-p/1549438#M6337...