Forecasting current month based on data so far

[mahawkins3]

Hi,

I'm looking to create a report that shows monthly totals for the last x months, then for the current month, I'd want it to be able to show a forecast of where the month will get to based on the numbers so far this month.

I have played around with the in-built forecasting tool, but that doesn't quite seem to do what I want. All that allows me to do is forecast the current (and following) months based on the numbers in previous months; it doesn't factor this month's numbers so far into the calculation.

So let's say it's April 16th, and we have data from Jan 1st up to yesterday:

January: 1,000,000

Feb: 2,000,000

March: 3,000,000

April (up to the 15th): 2,200,000

The way the forecasting tool works, if I tell it to ignore the last 1 month, it will give me a projection of 4,000,000 for April based on the growth from Jan-March. What I actually want it to do is forecast April based on that 2,200,000 so far. Using the simplest possible forecasting method, this would come out as 4,400,000.

Is there some way of doing this?

Thanks in advance,

Matt

[DoubleJ · Accepted solution]

Hi

I modified your sample data a bit as follows:

I created a measure called "ForecastMonthly"  that calculates the forecast (you might have to replace the semicolons with commas):

ForecastMonthly = 
CALCULATE( 
 DIVIDE(
 SUM(Revenue[Revenue]);
 DAY(MAX(Revenue[Date])) /*Last day with sales*/
 )
 * Day(EOMONTH(MAX(Revenue[Date]);0)); /*Days in Month*/
 FILTER(Revenue;EOMONTH(Revenue[Date];0)=EOMONTH(TODAY();0) /*Calculate the forecast only for the current month*/
))
+
CALCULATE(SUM(Revenue[Revenue]);
 FILTER(Revenue;EOMONTH(Revenue[Date];0) < EOMONTH(TODAY();0))) /*Calculate the revenue for the past months*/

Explanation:
The first part calculates the forecast for the current month by summing up the revenues. Then dividing the summed up revenues by the number of days for the last sale and then multiplying it with the total days of the month.
The second part simply sums up the revenues for all past months.

The result:

Hope this helps

JJ

[Carolyn_816]

This was very helpful, how would I go about forcasting out the rest of the year? I've tried so many things and I can't seem to get it to work. TIA

[Grahamwm]

This works well for forecasting current month. How can we expand this to forecast the current year using current months data. Lets say we have three months of cost data, how do we use that to forecast the current FY year.

[mahawkins3]

Worked a treat. Much appreciated. Knew there must be a way, but I'm completely new to DAX!