Adjust forecast to account for actuals to date

phiga — Mon, 06 Mar 2023 14:15:40 GMT

Hello,

I have a budgeting pbi file which shows future sales forecast and actual sales volume to date, along with a combined actual + forecast column

As can be seen from the sample data below the total yearly forecast is for 3,000 and YTD actuals is 400 ( i.e. 100 units below forecast - YTD delta)

I would like a method to calculate the YTD delta (difference between forecast and actual), calculate the number of remaining months (10 in the example) and then adjust the forecast value so that the yearly total of actual + adj forecast is equal to the original forecast for the year.

In the example below:
YTD delta is 100
remaining months = 10

Therefore Adj forecast would equal 250 + (100/10) = 260

Thanks in advance for any guidance

Date	Forecast	Actual	Actual + Forecast	Actual + Adj Forecast *(expected result)*
01/01/2023	250	200	200	200
01/02/2023	250	200	200	200
01/03/2023	250		250	260
01/04/2023	250		250	260
01/05/2023	250		250	260
01/06/2023	250		250	260
01/07/2023	250		250	260
01/08/2023	250		250	260
01/09/2023	250		250	260
01/10/2023	250		250	260
01/11/2023	250		250	260
01/12/2023	250		250	260
TOTALs	3000		2900	3000

Re: Adjust forecast to account for actuals to date

FreemanZ — Mon, 06 Mar 2023 14:49:48 GMT

hi phiga

not sure if i fully get your, you may try to add a calculated column like:

Column = IF( [Actual]<>BLANK(), [Actual], DIVIDE( SUMX(TableName, TableName[Forecast]) - SUMX(TableName,TableName[Actual]), DATEDIFF( MINX(FILTER(TableName, TableName[Actual]=BLANK()), TableName[Date]), MAXX(TableName, TableName[Date]), MONTH )+1 ) )

it worked like:

topic Adjust forecast to account for actuals to date in DAX Commands and Tips

Adjust forecast to account for actuals to date

Re: Adjust forecast to account for actuals to date