Pulling a Rolling Average - Dynamic Time

[kjanderson2]

Hello!

I'm seeing a lot of posts on this, however I'm not finding a solution for my specific situation. I've got 3 relevant columns : Date, Item, Sales. I want an average of the last 6 weeks sales by item. I need the average to read blank as 0, and the last 6 weeks is not as much time based as it is the last 6 weeks that are present in the data set.

The date may not be the value I use to filter the data -  I'm thinking I need to add a week column that would filter the 6 weeks between todays week (helper column) - 6, but I don't know if that's possible. I have nearly every week of data, there are just times that I don't have the past two weeks (data sent biweekly) and I need to pull the previous two weeks. 

I'm pretty new to all this so all help is appreciated. Please let me know what I can provide to help solve this. 

[v-juanli-msft · Accepted solution]

Hi @kjanderson2 

Create calculated columns

year-week = FORMAT([date],"yyyy-ww")

Create measures

sum per week =
CALCULATE (
    SUM ( Sheet3[value] ),
    FILTER (
        ALLSELECTED ( Sheet3 ),
        Sheet3[item]
            = MAX ( Sheet3[item] )
            && Sheet3[year-week]
                = MAX ( Sheet3[year-week] )
    )
)

sum last 6 weeks =
CALCULATE (
    SUM ( Sheet3[value] ),
    FILTER (
        ALLSELECTED ( Sheet3 ),
        DATEDIFF (
            Sheet3[date],
            TODAY (),
            WEEK
        ) < 6
            && Sheet3[item]
                = MAX ( Sheet3[item] )
    )
)


count_last6weeks =
CALCULATE (
    DISTINCTCOUNT ( Sheet3[year-week] ),
    FILTER (
        ALLSELECTED ( Sheet3 ),
        DATEDIFF (
            Sheet3[date],
            TODAY (),
            WEEK
        ) < 6
            && Sheet3[item]
                = MAX ( Sheet3[item] )
            && [sum per week] <> 0
    )
)

average = [sum last 6 weeks]/[count_last6weeks]

If there are no missing data

If there is missing data,

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.