# Calculate forecast for remainder of year



## nhoff (Jun 10, 2015)

I have

1) a date table (tblDates)
2) a table with forecasted and actual sales for every day (tblPrognosis). A column called 'Type' is used to distinguish between forecasted and actual sales

Using TOTALYTD formula I can calculate for a given date, what the total sales year to date is and compare it to the forecasted value:
YTDActuals:=TOTALYTD(SUM(tblPrognosis[Total]);tblDates[fldDate];tblPrognosis[Type]="Actuals";tblDates[fldDate] <= TODAY())
YTDForecast:=TOTALYTD(SUM(tblPrognosis[Total]);tblDates[fldDate];tblPrognosis[Type]="Forecast";tblDates[fldDate] <= TODAY())

I want to calculate the forecast for the rest of the year based on the specific date. A simplified example below, where I assume that I have forecasted a sales of 300/day:


Date        Sales         YTDActuals        YTDForecast     RestofYearForecast
1-jan       200           200                   300                 109.500
2-jan       350           550                   600                 109.200
3-jan       250           800                   900                 108.900

What formular can I use to calculate a running total for the rest of the year?


----------



## luirib (Jun 10, 2015)

nhoff said:


> I have
> 
> 1) a date table (tblDates)
> 2) a table with forecasted and actual sales for every day (tblPrognosis). A column called 'Type' is used to distinguish between forecasted and actual sales
> ...



Not sure I understand your issue very clearly, but when I need to mix actuals and forecast, I create a separate table (say PeriodStatus) with 2 columns: Period and Type. Period is YYYY-MM and type is either Actuals or Forecast. I create a relationship between this table and the Calendar table.

Then I add a filter to my formula so it takes into account what information to pull in (actuals or forecast). For example:
TotalAmount=CALCULATE(SUM(Actuals[Amount]), PeriodStatus[Type]="Actuals") + CALCULATE(SUM(Forecast[Amount]), PeriodStatus[Type]="Forecast")


----------



## nhoff (Jun 11, 2015)

Thanks for the answer.

Perhaps I did not explain myself very well and added too much information.

Basically I would like to create a formula that can show the forecast for the rest of the year (RestofYearForecast). Pulled in a pivot table as a calculated field and with dates on the left column I should be able to see forecast for each date. When I have the calculation for forecast, I can add Actuals until today (the calculated field "YTDActuals") I can more accurately predict end of year result.

If I understand your suggestion correct I can only show the forecast for a given period (YYYY-MM) and cannot granulate to date or e.g. week level?


----------



## luirib (Jun 11, 2015)

nhoff said:


> Thanks for the answer.
> 
> Perhaps I did not explain myself very well and added too much information.
> 
> ...



If you have both your actual and forecast information on the same table, I assume there is one column on it that tells them apart. If this is the case, you should have 2 measures, one for actuals (e.g. CALCULATE(SUM(Table[Amount]), Table[Type]="Actuals")) and another one for forecast (replace "Actuals" with "Forecast").

Regarding your question about at what level you can show your forecast, I assumed that you wanted to show by month but the granularity will depend on how detailed you have the forecast information and your preference.

If you want to show both Actuals and Forecast on the same report, I would suggest you use the technique I proposed on my last post. 

Let us know if you still need help.


----------

