cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mahawkins3 Regular Visitor
Regular Visitor

Forecasting current month based on data so far

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
DoubleJ Member
Member

Re: Forecasting current month based on data so far

Hi

 

I modified your sample data a bit as follows:

Forecast_01.PNG
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:


Forecast_02.PNG

Hope this helps

JJ

View solution in original post

2 REPLIES 2
Highlighted
DoubleJ Member
Member

Re: Forecasting current month based on data so far

Hi

 

I modified your sample data a bit as follows:

Forecast_01.PNG
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:


Forecast_02.PNG

Hope this helps

JJ

View solution in original post

mahawkins3 Regular Visitor
Regular Visitor

Re: Forecasting current month based on data so far

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 53 members 1,172 guests
Please welcome our newest community members: