Calculate Forecast for Current Month Sales with a Measure

[mbroering]

Hello!

I am wanting to create a measure that will calculate a forecast of the current months sales based on the MTD actual sales.  This formula must take into account the prior work day's MTD sales as I do not want to take into account today's data into account as those sales figures are not final.

I am trying to get to: Forecast Sales = (MTD Sales/# of actual MTD work days) * Current Month # of work days

For example, if todays date is Apr 8 2020, the number of MTD actual work days (not including today) = 5 days.  MTD sales through yesterday's date = $100,000.  And there are 22 work days in April.  I would expect my forecasted sales to be (100,000/5) * 22 = $440,000.

How can I create a measure that will update as time progresses throughout the month to calculated forecasted sales based on workdays?

Thanks so much!

[V-pazhen-msft]

@mbroering 

Try replace the measure with your table name and column names. 

Measure = 
var MTDsales 
=CALCULATE(SUM('Table'[Sales]),DATESMTD('Table'[Date]),ALL('Table'))

var MTDworkingdays 
=CALCULATE(COUNT('Table'[Date]),DATESMTD('Table'[Date]),FILTER(ALL('Table'),WEEKDAY([Date],2)<>6&&WEEKDAY([Date],2)<>7))

var Monthworkingdays
=CALCULATE(COUNT('Table'[Date]), FILTER(ALL('Table'),SUMX(FILTER('Table',EARLIER('Table'[Date].[Month])='Table'[Date].[Month]),1)),FILTER('Table',WEEKDAY([Date],2)<>6&&WEEKDAY([Date],2)<>7))

Return MTDsales / MTDworkingdays * Monthworkingdays 

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello, use that measure for my case which is very similar to initial, however I require you to screen me Saturdays too, where can I indicate that?

gives higher the forecast compared if I do it manually Sales to today/days billed * working days

but for the result to be given in mills I have to multiply it by 1000, what will I be doing wrong?

Forecast - var MTDsales

•CALCULATE(SUM(Tventas[Subtotal by Line]),DATESMTD(Tventas[DocDate]),ALL(Tventas))

were MTDworkingdays

=CALCULATE(COUNT(Tventas[DocDate]),DATESMTD(Tventas[DocDate]),FILTER(ALL(Tventas),WEEKDAY([DocDate],2)<>6&&WEEKDAY([DocDate],2)<>7))

were Monthworkingdays

•CALCULATE(COUNT(Calendar[DATE]), FILTER(ALL(Calendar),SUMX(FILTER(Calendar,EARLIER(Calendar[DATE].[ Month] ) -Calendar[DATE]. [Month]),2)),FILTER('Calendar',WEEKDAY([DATE],2)<>6&&WEEKDAY([DATE],2)<>7))

Return ((MTDsales / MTDworkingdays) * Monthworkingdays )*1000

[V-pazhen-msft]

@mbroering 

I tested with my sample with both dates and sales is on the same table. Try this with yours:

Measure = 
var MTDsales 
=CALCULATE(SUM(Sales[Sales]),DATESMTD(Sales[Date]),ALL(Sales))

var MTDworkingdays 
=CALCULATE(COUNT(Sales[Date]),DATESMTD(Sales[Date]),FILTER(ALL(Sales),WEEKDAY([Date],2)<>6&&WEEKDAY([Date],2)<>7))

var Monthworkingdays
=CALCULATE(COUNT(Calender[Date]), FILTER(ALL('Calender'),SUMX(FILTER('Calender',EARLIER('Calender'[Date].[Month])='Calender'[Date].[Month]),1)),FILTER('Calender',WEEKDAY([Date],2)<>6&&WEEKDAY([Date],2)<>7))

Return MTDsales / MTDworkingdays * Monthworkingdays 

If not working, I would be best if you can share your sample pbix.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mbroering]

@V-pazhen-msft 

Thank you for the instructions, I am trying to replicate now.  As part of the MTD Sales formula, the part of the formula "ALL('Table'), which table is this in reference to?  Is it the calendar table or the sales table?

[amitchandak]

@mbroering , Assume you have a working day in the calendar , you can work like

(CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))/CALCULATE(sum('Date'[Working Day]),DATESMTD('Date'[Date])))*
CALCULATE(Sum('Date'[Working Day]),filter(all('Date'),format('Date'[Date],"YYYYMM")=format(today,"YYYYMM")))