Forecast/Actuals Calculations

[Anonymous]

Hi All, I have tw tables Actuals and forecast that are connected through calendar table via calendar date.

I want to create a matrix where I can see one column that represents Actual/Forecast i.e. if actuals are not available then forecast amount should be added but if actual is available then only actuals should be updated in column.

Below is the expected output.

Year	Qtr	Actuals	Forecasted	Forecasted/Actuals
2022	Q1	500	300	500
2022	Q2	600	400	600
2022	Q3	0	150	150
2022	Q4	0	500	500
		1100	1350	1750

I tried to create a dax but not getting the correct output

Actuals/Forecast =

IF (

    HASONEVALUE (Date Dim'[calendar date]),

    IF ( [Actuals] = 0,[Forecast Amount],[Actuals] ),

    SUMX ('RPT Date Dim',

        IF ( [Actuals] = 0, [Forecast Amount],[Actuals] )

    )

Please help.

@Greg @amitchandak @Pragati11 

[lbendlin · Accepted solution]

You can do this much easier if you keep the Actuals blank (rather than at 0)

Forecasted/Actuals = COALESCE([Actuals],[Forecasted])