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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bnisbet
Frequent Visitor

IF Statement Custom Column

Hey,

 

Trying to create a Custom Column for an IF Calculation, but I keep getting an Error?

 

Finanical Year = IF ( MONTH (Opportunity[ActualCloseDate]) <4, "0", "1") + Year(Opportunity[ActualCloseDate])

 

Thanks!   

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@bnisbet

What kind of error are you getting?

There's nothing wrong with this DAX formula (it should work with or without the quotes "0", "1" or 0, 1)

 

Financial Year = IF ( MONTH ( 'Calendar'[Date] ) < 4, "0", "1" ) + YEAR('Calendar'[Date])

Are you maybe doing this in the Query Editor by any chance? There the syntax is different

https://msdn.microsoft.com/en-us/library/mt296606.aspx

 

Financial Year = (if Date.Month([Date]) < 4 then 0 else 1) + Date.Year([Date])

Hope this helps.

Good Luck! Smiley Happy

View solution in original post

6 REPLIES 6
pszczolka1984
New Member

Hi Guys,

 

I am trying to add a column that will contain if statment in query editor: if column Name contains a word "FALSE" than show me "False" if not show "n/a". At the minute I am getting a token literal error even if I only type =if.

I would be grateful for your help.

Sean
Community Champion
Community Champion

@bnisbet

What kind of error are you getting?

There's nothing wrong with this DAX formula (it should work with or without the quotes "0", "1" or 0, 1)

 

Financial Year = IF ( MONTH ( 'Calendar'[Date] ) < 4, "0", "1" ) + YEAR('Calendar'[Date])

Are you maybe doing this in the Query Editor by any chance? There the syntax is different

https://msdn.microsoft.com/en-us/library/mt296606.aspx

 

Financial Year = (if Date.Month([Date]) < 4 then 0 else 1) + Date.Year([Date])

Hope this helps.

Good Luck! Smiley Happy

bnisbet
Frequent Visitor

@Sean

 

Yeah was trying to do it in Query Editor - Thanks for the link!

Much Appericated!!!

 

In the Query Editor - how would I apply a check to only run the IF, if the column contained data?

Sean
Community Champion
Community Champion

@bnisbet

 

I'm not sure if this is what you're looking for...

 

Financial Year = if [Date] = null then null else (if Date.Month([Date]) < 4 then 0 else 1) + Date.Year([Date])

Good Luck! Smiley Happy

 

Otherwise I suspect @MarcelBeug may have something to say! Smiley Happy

MarcelBeug
Community Champion
Community Champion

Thanks @Sean

 

This would be an alternative:

Date.Year(Date.AddMonths([Date],9))

 

 

No need to test for null, if [Date] is null then the result will be null.

Specializing in Power Query Formula Language (M)
bnisbet
Frequent Visitor

Works perfect thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.