ROLLING FORWARD AVERAGE USING WK AND BY PRODUCT

[Jelto83]

Hello to anyone who can help!

After trawling the internet for hours now trying to source a solution to the following problem; but nothing seems to have covered the criteria of what it is that I'm after.

In essence I am trying to get the next 4 weeks average forecasted sales (moving average) by week number (no dates) for each product. Please note I am using Power Pivots and Data Connections in Excel as we don't have access to PowerBI in my business yet but I've have used it and have faily basic DAX skills.

Below shows a sample of data; So for Week Index = 1 Product DT10072, I want to see the average of Weeks Index 2-5 for that Product (DIV SKU) only.

I've tried as adding as a measure but it just brings back the same weeks values. (please note filters are only on for to show sample of data accordingly - these won't be applied in productions 

THANKS IN ADVANCE!!!

[AlB · Accepted solution]

Hi @Jelto83 

I understand it is a calculated column that you're after?  Try this:

New col =
VAR currentWeek_ = Table1[Week INDEX]
RETURN
    CALCULATE (
        AVERAGE ( Table1[FcastSales] ),
        ALLEXCEPT ( Table1, Table1[DIV SKU] ),
        Table1[Week INDEX] >= ( currentWeek_ + 1 ),
        Table1[Week INDEX] <= ( currentWeek_ + 4 )
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Jelto83]

Thanks @AlB - It didn't have to be a new column but I can work with that and it worked - many thanks!!!!