Forecast sales for each (month) and (Year)

[gauravnarchal]

Hello, I want to get the forecast for sales for each (month) and (Year).

While forecasting, I want to consider only 20% of sales for Saturday and Sunday, and all other days can be 100%.

I have the PBI file with the sample data for your reference. Click Here

[v-yangliu-msft · Accepted solution]

Hi  @gauravnarchal ,

Shared link time expired, I created some data:

Here are the steps you can follow：

1. Create calculated column.

year = YEAR('Table'[date])

Month = MONTH('Table'[date])

week = WEEKDAY('Table'[date],2)

IF =
IF(
    'Table'[week]>=1&&'Table'[week]<=5,1,0)

2. Create measure.

Month forecastA =
var _avgamount=CALCULATE(AVERAGE('Table'[amount]),FILTER(ALL('Table'),'Table'[year]=MAX('Table'[year])&&'Table'[Month]=MAX('Table'[Month])&&'Table'[IF]=1))
var _day=COUNTX(FILTER(ALL('Table'),'Table'[year]=MAX('Table'[year])&&'Table'[Month]=MAX('Table'[Month])&&'Table'[IF]=1),[date])
return
DIVIDE(_avgamount,_day)

Month forecastB =
var _amount=CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[year]=MAX('Table'[year])&&'Table'[Month]=MAX('Table'[Month])&&'Table'[IF]=0))
var _day=COUNTX(FILTER(ALL('Table'),'Table'[year]=MAX('Table'[year])&&'Table'[Month]=MAX('Table'[Month])&&'Table'[IF]=0),[date])
return
DIVIDE(_amount * 0.2,_day)

Month forecast =
var _amount=CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[year]=MAX('Table'[year])&&'Table'[Month]=MAX('Table'[Month])))
return
[Month forecastA]+[Month forecastB] + _amount

Year forecastA =
var _avgamount=CALCULATE(AVERAGE('Table'[amount]),FILTER(ALL('Table'),'Table'[year]=MAX('Table'[year])&&'Table'[IF]=1))
var _day=COUNTX(FILTER(ALL('Table'),'Table'[year]=MAX('Table'[year])&&'Table'[IF]=1),[date])
return
DIVIDE(_avgamount,_day)

Year forecastB =
var _amount=CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[year]=MAX('Table'[year])&&'Table'[IF]=0))
var _day=COUNTX(FILTER(ALL('Table'),'Table'[year]=MAX('Table'[year])&&'Table'[IF]=0),[date])
return
DIVIDE(_amount * 0.2,_day)

Year forecast =
var _amount=CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[year]=MAX('Table'[year])))
return
[Year forecastA]+[Year forecastB]+_amount

3. Result:

Please click here for the pbix file

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[parry2k]

@gauravnarchal what is the logic for the forecast? 

[gauravnarchal]

Hi @parry2k  - Here is the logic which I wanted to build

Month forecast

A          =          Average total sales of the month on weekdays (Monday to Friday) multiplied by the remaining number of weekdays

B          =          20% of total sales of the month on weekends (Saturday & Sunday) multiplied by the remaining number of weekend days

Forecast = Total Sales o fthe month + A + B

Yearly forecast

A          =          Average total sales of the year on weekdays (Monday to Friday) multiplied by the remaining number of weekdays

B          =          20% of total sales of the year on weekends (Saturday & Sunday) multiplied by the remaining number of weekend days

Forecast = Total Sales of the year + A + B