Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculated Column not working

I have a calculated column using the formula below.......the result returned is not as expected. 

ACT/FC = IF([FP Year]<[Current Year]||[FP Month]<[Current Month],"Actual","Forecast")
 

The value returned for Jan 23 - Sep 23 should be "Forecast" but its returning "Actual"??

 

Can anyone help? Thanks

Need Help
  • hnguy71's avatar
    hnguy71
    3 years ago

    Hi Anonymous ,

    Try this instead:

    ACT/FC = 
VAR _FPDate = DATE([FP Year], [FP Month], 1)
VAR _CurrDate = DATE([Current Year], [Current Month], 1)

RETURN

IF(_FPDate < _CurrDate, "Actual", "Forecast")

     

     

5 Replies

  • hnguy71's avatar
    hnguy71
    Super User
    3 years ago

    Hi Anonymous ,

     

    I think you need to adjust your formula to use AND instead of OR:

     

    ACT/FC = IF([FP Year] < [Current Year] && [FP Month] < [Current Month], "Actual", "Forecast")
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thanks but that hasnt worked either, result below using AND

     

     

    • hnguy71's avatar
      hnguy71
      Super User
      3 years ago

      Hi Anonymous ,

      Try this instead:

      ACT/FC = 
VAR _FPDate = DATE([FP Year], [FP Month], 1)
VAR _CurrDate = DATE([Current Year], [Current Month], 1)

RETURN

IF(_FPDate < _CurrDate, "Actual", "Forecast")

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Thanks but that didnt work either, delivered the same result as the original formula