Combining sales and forecast in one column, forecast not aggregating

[u49947368]

I am trying to get the actuals and forecast into one column so I can plot it in a trendline where the actuals simply overflow into forecast. 

I got one main table including Finance Date, Actuals, and Forecast. It looks like this: 

Finance Date	Actuals	Forecast
10-2021	100	200
11-2021	150	250
12-2021	0	200
01-2021	0	230

I created a simple dax to get the actuals only untill the date that we have actuals (so exluding zeros), and I got the remaining forecast. The two dax look like this: 

Sales = IF ( Main[Actuals] <> 0, Main[Actuals], BLANK ()

Remaining forecast = IF ( Main[Actuals = 0, Main[Forecast], BLANK ()

Initialy, this looks like it is correct, but when I plot it in a table, the forecast is not aggregating: 

Finance Date	Actuals	Forecast	Remaining Sales	Remaining Forecast
10-2021	100	200	100
11-2021	150	250	150
12-2021	0	200		200
01-2021	0	230		230
Total	250	880	250	THIS REMAINS BLANK

[v-cazheng-msft · Accepted solution]

Hi @u49947368 

Please try to use this formula to calculate your Remaining Forecast.

Remaining forecast =

VAR selectValue =

    IF ( Main[Actuals] = 0, Main[Forecast], INT ( BLANK () ) )

RETURN

    selectValue

After that, use this Calculated column to combine sales and forecast.

CombiningValues = IF(Main[Actuals]=0,Main[Remaining forecast],Main[Remaining Sales])

Result should look like this:

Attached the pbix file as reference.

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it or I misunderstand your needs, please feel free to let us know. Thanks a lot!

[HotChilli]

"I am trying to get the actuals and forecast into one column" It looks like you've still got them in 2 columns.

--

If you're keeping 2 columns and the problem is that there is no total for the Remaining Forecast calculated column, check that you have the correct datatype and check that the summarization is Sum on the column.

[amitchandak]

@u49947368 , Try like

sumx(values(Date[financial Date]), IF ( [Actuals] = 0, [Forecast], BLANK ()) )