Forecast/Actuals Calculations

Anonymous — Wed, 14 Sep 2022 11:36:16 GMT

Hi All, I have two tables Actuals, and forecasts connected through a 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 the forecast amount should be added but if actual is available then only actuals should be updated in the 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

Re: Forecast/Actuals Calculations

Pragati11 — Wed, 14 Sep 2022 14:18:48 GMT

HI Anonymous ,

Is it possible for you share a picture of your data model here? I want to see how these tables are related to each other and on what columns the relationship has been made.

Also share some sample data if possible.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump-to/first-unread-message

topic Forecast/Actuals Calculations in DAX Commands and Tips

Forecast/Actuals Calculations

Re: Forecast/Actuals Calculations