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

mahawkins3 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 #### 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. #### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work Top Kudoed Authors
Users Online
Currently online: 355 members 3,061 guests
Recent signins:
• tconrad • Jpalji • rdavis61 • roxieli123 • maggigfrench • PANDAmonium • RApbix • jeags • amirjabri • edavidso • ALvanti • catrayl • Diego-CDMX • zrandolph 