cancel
Showing results for
Did you mean:
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?

Matt

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: Forecasting current month based on data so far

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

2 REPLIES 2
Member

## Re: Forecasting current month based on data so far

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

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!

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 5 members 1,696 guests
Recent signins: