Skip to main content

Hi, I want to create a formula using IF, similar to that: if(Month.'End Date'>'Switchover date','metric XX'), but I have 2 conditions:

  1. Month.'End Date'>'Switchover date’, and
  2. If it is 2022 it has to get data from metric A and if it is from 2023 onwards it has to get data from metric B

How can I do that?

Hi @Carolina Lee 

If you only have two conditions, can’t you just do:

IF(Month.'End Date'>'Switchover date', Metric A, Metric B)

If not, please can you provide more context (e.g. does your switchover date vary by version? Are there actually 3 conditions, etc)


I am using period type, such as Actual and Budget.

For Actual, my formula is: IF(Month.'End Date'<'Switchover date', Metric Actuals)

 

But for Budget I have budget 2022 and budget 2023. So I need to do a formula in which it considers Month.'End Date'>'Switchover date'  and these 2 conditions: ‘metric budget 2022’ for the year 2022 and ‘metric budget 2023’ for the year 2023 onwards.

 


Hi @Carolina Lee 

To allow me to provide you the right answer, can you give me more details :

  • Do you want to have the Actuals, Budget FY22 & FY23 in the same Metric ?
  • Are you using ‘Version’ dimension ? Or native scenarios ?
  • Do you have only one ‘Switchover date’ for the Actuals, Budget FY22 & FY23 ?
  • Do you have only FY22 data on the Budget FY22 ? Same for Budget FY23

Assuming the context, you can try a formula with the following base :

IF(Month.'End Date'>'Switchover date', Budget2022+Budget2023)


Let me know if this helped you, or if you have more questions.


Reply