Forum Discussion
ROLLING FORWARD AVERAGE USING WK AND BY PRODUCT
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!!!
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
2 Replies
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
