Forum Discussion
Forecast/Actuals Calculations
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.
1 Reply
- Pragati11
Super User
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.
