Forum Discussion

Harry_Tran's avatar
Harry_Tran
Helper III
5 years ago
Solved

Rolling average missing values

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 ta...
Need Help
  • Icey's avatar
    Icey
    5 years ago

    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.

Icey's avatar
Icey
Community Support
5 years ago

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.