Calculate forecast for remainder of year

[nhoff]

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]

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?

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]

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]

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?

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.