Adjust forecast to account for actuals to date

[phiga]

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

[FreemanZ]

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: