Moving Average of previous 4 weeks by using the week number

[Anonymous]

Hello everyone, 

Im trying to calculate the moving average of sales of the previous 4 weeks by using the week number. In addition, my database consists of many entrys, that contain the the number of the week, the year, the retail that is selling, the store of the retail and the SKU of the product. 

Thanks everyone for the help!

[Anonymous]

Here is a sample of the database: 

Year	Week	Retail	Store	SKU	Sell (units)
2022	12	A	12	TV50INCH	5
2022	12	A	43	TV42INCH	10
2022	13	A	43	TV50INCH	7
2022	13	A	12	TV50INCH	15

Assuming for this case, many number of SKU, retail and stores, i would like to add a column that gives me the sales average for each entry. In this case, for the newest entry (W14, and obviously for the previous ones) i would like to have something like this (in this case moving average of 2 weeks due to this short table):

Year	Week	Retail	Store	SKU	Sales (units)	SalesAVG(2weeks)
2022	14	A	12	TV50INCH	7	10 ((15+5)/2))

Best regards for all of you!

[v-cazheng-msft · Accepted solution]

Hi @Anonymous ,

Per the description, I create some sample data and do calculation based on it.

Create a Measure to calculate average of last two weeks.

SalesAVG(TwoWeeks) =
CALCULATE (
    AVERAGE ( 'Table'[Sell (units)] ),
    FILTER (
        ALLEXCEPT (
            'Table',
            'Table'[Year],
            'Table'[Store],
            'Table'[Retail],
            'Table'[SKU]
        ),
        (
            'Table'[Week]
                >= MAX ( 'Table'[Week] ) - 2
        )
            && ( 'Table'[Week] < MAX ( 'Table'[Week] ) )
    )
)

Then, the result looks like this.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[Whitewater100]

Hello:

Please see the file for full solution.https://drive.google.com/file/d/1WzVWHmsjXXe_ncwOLQnRZh00W2WOvO11/view?usp=sharing 

I wanted to mention I added some tables that could be helpful to you. You will notice the Sales(Fact table) has a couple of columns you would typically delete but I left them there so you can see what is going on. 

Essentially, we created a ID field to connects your stores and products to their own dimension table. I added in some more data, a store and a product or two.

You could use the fields from the stores or products table to create visuals(vs. from Fact Table). The date calendar offers you a lot of flexibilty as the current week number is at zero and it tells you if week is complete or not.(See Filter panel). It is joined on date but could join to sales on wek number but date is generally where to connect, if you get sales at that grain.  The Date Table is continuous and marked as a date table. Here is a picture of results:

Basically just broke up a flat file with everything and normalized it a bit.