Forum Discussion

Chaucer's avatar
Chaucer
Helper II
6 years ago
Solved

Forecasting Sales

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!

Need Help
  • Sujit_Thakur's avatar
    Sujit_Thakur
    6 years ago

    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's avatar
    Chaucer
    6 years ago

    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)

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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-Last-Week/ba-p/1051123

     

    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

  • Sujit_Thakur's avatar
    Sujit_Thakur
    Solution Sage
    6 years ago

    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's avatar
    Chaucer
    Helper II
    6 years ago

    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's avatar
      Sujit_Thakur
      Solution Sage
      6 years ago

      Dear Chaucer 

       You can select multiple post as solution 

      And i hope you will do that justice .

       

      Regards 

      Sujit Thakur