Problem with Forecast - DAX function

[IrfanYasar]

I have the following problem that I unfortunately cannot solve.

In a table with actual, budget and forecast data, there are 3 forecasts per table (FC 1, FC 2 and FC 3).

If I solve the problem with a measure using an IF query, it only works in the current month - the cumulative value is not correct.

Type	MMM.JJJJ	Value
Actual	006.2022	10
Budget	006.2022	15
FC 1	006.2022	12
FC 2	006.2022	13
FC 3	006.2022	15
Actual	007.2022	20
Budget	007.2022	22
FC 1	006.2022	15
FC 2	006.2022	25
FC 3	007.2022	22
Actual	008.2022	0
Budget	008.2022	10
FC 1	008.2022	10

var _FC1 = CALCULATE(SUM('MIK_FZG'[Value]), 'MIK_FZG'[Type] IN { "FC 1" })
var _FC2 = CALCULATE(SUM('MIK_FZG'[Value]), 'MIK_FZG'[Type] IN { "FC 2" })
var _FC3 = CALCULATE(SUM('MIK_FZG'[Value]), 'MIK_FZG'[Type] IN { "FC 3" })

return
IF(ISBLANK(_FC3), IF(ISBLANK(_FC2),_FC1,_FC2), _FC3)

--> this formula only works when viewed on a monthly basis.

Target:
Always take FC 3 for past months and only the current FC for the current month.
(Whether Measure or Calculating column or Power Query)

Anybody has any ideas or tips?

Thanks in advance for the lesson 😊

[v-henryk-mstf · Accepted solution]

@IrfanYasar ,

Try formula like below:

M_ = 
IF (
    MAX ( 'Table'[Type] ) = "FC 1",
    SUM ( 'Table'[Value] ),
    IF (
        MAX ( 'Table'[Type] ) = "FC 2",
        SUM ( 'Table'[Value] ),
        IF (
            MAX ( 'Table'[Type] ) = "FC 3",
            SUM ( 'Table'[Value] ),
            CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[MMM.JJJJ],'Table'[Type] ) )
        )
    )
)

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[IrfanYasar]

Dear Henry


Thank you very much for your answer and your suggestion!

With your formula it calculates for me "Actual" + "Budget" +"FC 1" + "FC 2" + "FC 3" (in 008.2022 only the FC 1).

006.2022 - 008.2022 = 189
008.2022 = 20

I have (unfortunately) other filters that I have inserted in the "ALLEXCEPT" so that they are drawn correctly, can it be related to this?

Best Regards,
Irfan

[v-henryk-mstf]

Hi @IrfanYasar ,

Yes, you can add as many filtering contexts as you want to keep in the allexcept function.

For more details, you can read related blog.

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[IrfanYasar]

It sums with your formula current, budget and forecast

[IrfanYasar]

How could I possibly solve it in a calculating column?

[amitchandak]

@IrfanYasar , try like

var _FC1 = CALCULATE(SUM('MIK_FZG'[Value]), filter('MIK_FZG','MIK_FZG'[Type] IN { "FC 1" }))
var _FC2 = CALCULATE(SUM('MIK_FZG'[Value]), filter('MIK_FZG','MIK_FZG'[Type] IN { "FC 2" }))
var _FC3 = CALCULATE(SUM('MIK_FZG'[Value]), filter('MIK_FZG','MIK_FZG'[Type] IN { "FC 3" }))

return
IF(ISBLANK(_FC3), IF(ISBLANK(_FC2),_FC1,_FC2), _FC3)

[IrfanYasar]

Thanks for the quick answer, I will try it right away.

Ok, I tried it, but unfortunately it still does not calculate correctly:

FC = 15 (FC 3) + 22 (FC 3) + 10 (FC 1) = 47
--> I should get cumulated.

With your formula, I get for 008.2022 = 10
Cumulated from 006.2022 to 008.2022 = 37

[IrfanYasar]

Not sure if it works at all with a measure, as it always has a cumulative value for each FC (FC 1, 2 and 3)...