cancel
Showing results for
Search instead 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?

Thanks in advance,

Matt

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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
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!

## Helpful resources

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and 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.

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 53 members 1,172 guests
Recent signins:
Please welcome our newest community members: