Create a Nested IF Else calculated key figure (KF) that incorporates following elements

[SoniaMarwah]

If current period is current period-1, then use actuals. Otherwise use snapshot 1. if snapshot 1 is null then use snapshot 2. 

ACTUALIZEDKF1@MTHPRODLOCCUST= IF( "PERIODID3" < "$$PERIODID3CU$$" -1, "ACTUALSQTY@MTHPRODLOCCUST",IF(ISNULL("snapshot 1@MTHPRODLOCCUST"),"snapshot 2@MTHPRODLOCCUST","snapshot 1@MTHPRODLOCCUST"))

Is there any issue with the formula? It is working fine with some exceptions where I have 0 in snapshot 1. I kept the system as "Uneditable" and no Aggregation Mode

[vincentverheyen]

Hi @SoniaMarwah,

The first thing I would like to add is that you are using IF("PERIODID3" < "$$PERIODID3CU$$" -1,... and not IF("PERIODID3" = "$$PERIODID3CU$$" -1,... The former is slightly different from your wording ''If current period is current period-1, then... otherwise...'.

With regards to your main query, you can build in another condition to check for "snapshot 1@MTHPRODLOCCUST" being equal to 0. Please note that NULL and 0 are not the same. It sounds like, in your case, you want to have both conditions used in your calculation.

I hope this helped.

Kind regards,

Vincent Verheyen

[SoniaMarwah]

@vincentverheyen: If "snapshot 1@MTHPRODLOCCUST" is NULL, use "snapshot 2@MTHPRODLOCCUST". Otherwise, if "snapshot 1@MTHPRODLOCCUST" is 0 or any other value, keep "snapshot 1@MTHPRODLOCCUST". Here is the formula in question: ZWEACTUALIZEDCSP1@MTHPRODLOCCUSTWE = IF("PERIODID3" = " 𝑃 𝐸 𝑅 𝐼 𝑂 𝐷 𝐼 𝐷 3 𝐶 𝑈 PERIODID3CU"-1, "ZWEACTUALSQTY@MTHPRODLOCCUSTWE", IF(ISNULL("ZWECSPSNAPSHOT_CSP_1@MTHPRODLOCCUSTWE"), "ZWECSPSNAPSHOT_CSP_2@MTHPRODLOCCUSTWE", "ZWECSPSNAPSHOT_CSP_1@MTHPRODLOCCUSTWE"))

[SoniaMarwah]

Would the above calculation work as intended?

[vincentverheyen]

Hi @SoniaMarwah, if you need further assistance, can you please show me a screenshot of your calculation and a screenshot of your result in the IBP Planning View, and what is not working as expected. It will be easier this way.