Forecasting Sales

[Chaucer]

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!

[Sujit_Thakur · Accepted 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 Calculating Sales
using DAX to give a Forecast by average for next year
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 · Accepted solution]

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!

Forecast Pallets =

VAR

PalletsLY = Calculate([Total Pallets],DATEADD( Dates[date],-1,year))

VAR

Pallets2Y = Calculate([Total Pallets],DATEADD( Dates[date],-2,year))

VAR

Prior3WeeksPallets = calculate ([Total Pallets],datesbetween(Dates[Date],Today()-22,Today()-1))

Return

Divide(PalletsLY + Pallets2Y + Prior3WeeksPallets,5,0)

[Sujit_Thakur]

Dear @Chaucer 

 You can select multiple post as solution 

And i hope you will do that justice .

Regards 

Sujit Thakur

[Sujit_Thakur · Accepted 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 Calculating Sales
using DAX to give a Forecast by average for next year
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 

[amitchandak]

@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

[Ajinkya369]

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