Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!!
Solved! Go to 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
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
Thanks @AlB - It didn't have to be a new column but I can work with that and it worked - many thanks!!!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |