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.
So, I want to create a table that forecasts the next 4 weeks sales by SKU. Table will look like this:
Week Commencing\ SKU | SKU A | ... |
11/08/2020 | A | |
18/08/2020 | B | |
25/08/2020 | C | |
01/09/2020 | D |
The forecasts (A,B,C,D) will be based on
the average weekly sales for the last 4 weeks, plus
the average weekly sales for the 3 weeks from the previous year of the relevant week commencing.
I'm trying to get my head around how that formula looks, and not sure I'm winning! My assumption is that I create a Measure that looks something like this:
divide(calculate(sum('Order Lines'[Pallet Count]),datesbetween(Dates[Date],Today()-1,Today()-29)),4)
And then add to it another measure like this for the second part (Can I combine these in 1 measure?)
divide(calculate(sum('Order Lines'[Pallet Count]),datesbetween(Dates[Date],'Dates'[Week Comencing]-372,'Dates'[Week Commencing]-351))),3)
The DATESBETWEEN function doesn't seem to want to take my week commencing values though?
Help very gladly received!
Solved! Go to Solution.
Dear @Chaucer ,
You can use following measures , just instead of years you can use week as you want to forecast sales for 3 weeks ahead
Image has DAX for last 3 years and forecast for next year ,you can use it for week also
if image is not clear
VAR sales_last_year = Calculate([Total Sales],DATEADD(Date[Date] , -1 ,Year))
Such more 2 measures for calculating Sales for 3 years then
use
return
Divide all three variable (lastyear,2years back,3 years back) by 3 you will get forecast for next year
remeber you need to replace years by week
For more info you can check video which will help you a lot
https://www.youtube.com/watch?v=vq3VOERJw7s
I would expect a kudos .
and if this helped getting close to your solution accept it as a solution
if any further help required please let me know , i think you can nail this problem with my recommended youtube video.
Regards ,
Sujit Thakur
Thanks all for your help.
Using a combination of the links you guys added I knocked this up which works a treat. If i could select all as solution I would!
Thanks all for your help.
Using a combination of the links you guys added I knocked this up which works a treat. If i could select all as solution I would!
Dear @Chaucer
You can select multiple post as solution
And i hope you will do that justice .
Regards
Sujit Thakur
Dear @Chaucer ,
You can use following measures , just instead of years you can use week as you want to forecast sales for 3 weeks ahead
Image has DAX for last 3 years and forecast for next year ,you can use it for week also
if image is not clear
VAR sales_last_year = Calculate([Total Sales],DATEADD(Date[Date] , -1 ,Year))
Such more 2 measures for calculating Sales for 3 years then
use
return
Divide all three variable (lastyear,2years back,3 years back) by 3 you will get forecast for next year
remeber you need to replace years by week
For more info you can check video which will help you a lot
https://www.youtube.com/watch?v=vq3VOERJw7s
I would expect a kudos .
and if this helped getting close to your solution accept it as a solution
if any further help required please let me know , i think you can nail this problem with my recommended youtube video.
Regards ,
Sujit Thakur
@Chaucer , refer these two
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
Using Week Rank
Last 3 week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-3 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Last 3 week Avg Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-3 && 'Date'[Week Rank]<=max('Date'[Week Rank])))/3
Hi @Chaucer ,
Go through this video to solve your problem:
https://www.youtube.com/watch?time_continue=118&v=tztrdW7qTEU&feature=emb_logo
If your problem is solved then accept this as solution
Thank you
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |