DAX Formula Not Working?

[gino59]

Hi all,

Dipping my toes into DAX and hoping someone can look at this formula and tell me where I'm going wrong! First - the formula works - well, sort of...

I have a column of dates and a column with just the related Month name (JAN, FEB, MAR, etc.). I added a new calculated column that should return a value like 01 JAN, or 02 FEB, 11 NOV. The formula flys through the table but returns 1 JAN, 2 FEB, 11 NOV - not the 01 JAN I'm expecting.

So here's the formula...

=if(MONTH([Date]>9),MONTH([Date])&" "&[Month],"0"&MONTH([Date])&" "&[Month])

I think I'm saying if the month number of the date is greater than 9, return the month number plus a couple of spaces and the value from the Month column (11 NOV e.g.) and if the value of the month number of the date is less than 10, return a 0 and then the month number, a couple of spaces and the month name (01 JAN).

Any ideas on where I'm messing up???

Many many thanks!!
Gino

 

[MD610]

The criteria should be MONTH([Date])>9.

That being said, this is a simpler way to get the same result:

=FORMAT(MONTH([Date]), "00")&" "&[Month]

 

[gino59]

Brilliant, MD610 - yeah, I have this weird tendency in Excel to make my life more difficult with complex formulas!

Thanks a lot for the much simpler formula!

Cheers,
Gino