Forum Discussion

kjanderson2's avatar
kjanderson2
Regular Visitor
6 years ago
Solved

Pulling a Rolling Average - Dynamic Time

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. 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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.