Help with creating automated rolling week

[Anonymous]

Hi guys. Hoping you can help me with this query, I have been looking and haven't seen a solution yet.

So I have a database that includes, among others, a colour for Year (can be 2018 or 2019) and Week. For 2018 I have 53 weeks, and 2019 is being updated every week so every week there is a new week being added. I need to compare weekly same period of 2018 and 2019 for several metrics. So this week I am comparing sales for week 20 in 2018 and week 20 in 2019, as well as week 1 to week 20 in 2018 and 2019.

As I mentioned this is updated weekly, and I would like to avoid filtering manually on a weekly basis, I would like to automate it as much as possible.

How do I create a metric/column that helps me filter same week for 2018 and same period for 2018 (week 1 to last available week, as this will vary weekly).

Hope I'm being clear, apologies if not!

[v-cherch-msft]

Hi @Anonymous 

You may refer to below measure.It could get the rolling week sum for 5 weeks for 2019.For example:

2019_sales = 
IF (
    MAX ( Table1[Year] ) = 2019,
    CALCULATE (
        SUM ( Table1[Sales] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Year] = 2019
                && Table1[Week]
                    >= MAX ( Table1[Week] ) - 4
                && Table1[Week] <= MAX ( Table1[Week] )
        )
    )
)

Regards,

[Anonymous]

Thanks - this is helpful and helps me calculate it for 2019. However, it doesn't allow me to calculate it for the previous year (same period) for comparison?

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You could create the second measure as below:

sales_ 2018 = 
CALCULATE (
    SUM ( Table1[Sales] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Year]
            = MAX ( Table1[Year] ) - 1
            && Table1[Week]
                >= MAX ( Table1[Week] ) - 4
            && Table1[Week] <= MAX ( Table1[Week] )
    )
)

Regards,