Forum Discussion

mahawkins3's avatar
mahawkins3
Helper I
9 years ago
Solved

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 t...
Need Help
  • DoubleJ's avatar
    DoubleJ
    9 years ago

    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

DoubleJ's avatar
DoubleJ
Solution Supplier
9 years ago

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.