Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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")
 
SM4437_0-1666729705371.png

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

 

Can anyone help? Thanks

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

 

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Thanks but that hasnt worked either, result below using AND

SM4437_0-1666730389562.png

 

 

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

 

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!
Anonymous
Not applicable

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

Anonymous
Not applicable

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

hnguy71
Memorable Member
Memorable Member

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


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.