IF Statement Custom Column

[bnisbet]

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!   

[pszczolka1984]

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 · Accepted solution]

@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!

[bnisbet]

@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]

@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!

Otherwise I suspect @MarcelBeug may have something to say!

[MarcelBeug]

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.

[bnisbet]

Works perfect thank you!