Calculated Column not working

[Anonymous]

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

[Anonymous]

Thanks but that hasnt worked either, result below using AND

[hnguy71 · Accepted solution]

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]

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

[Anonymous]

Sorry made a typo, yes this formula has worked ok, thanks for your help

[hnguy71]

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")