PowerPivot DAX strange behavior
-
Thursday, January 13, 2011 11:35 AM
Could somebody please give me a reasonable explanation to why the following DAX returns a strange value?
=FORMAT(YEAR(2011-01-10),"YYYY")&FORMAT(MONTH(2011-01-10),"MM")
Just logical thinking this would return 201101 but it returns 190501
I already know how to do this properly by using =FORMAT(2011-01-10,"YYYYMM")
But I am a bit curious to know how you do when you want to concatenate a date and a text string will this also show wrong result?
ELVIS_OF_BI
All Replies
-
Friday, January 14, 2011 10:10 AM
Hi
Excel is seeing it as Year(2011) where 2011 as a serial day number is 03 Jul 1905
You don't need the Year and Month functions at allWhat you should use is
=FORMAT(--"2011-1-10","YYYY")&FORMAT(--"2011-1-10","MM")Enclosing the date in " " makes it a text string. Putting the double unary minus -- in from of it turns it to a serial number
Regards
Roger Govier"ELVIS_OF_BI" wrote in message news:8d52a97a-321d-4011-9e2f-5e9aa40b8f5b@communitybridge.codeplex.com...
Could somebody please give me a reasonable explanation to why the following DAX returns a strange value?
=FORMAT(YEAR(2011-01-10),"YYYY")&FORMAT(MONTH(2011-01-10),"MM")
Just logical thinking this would return 201101 but it returns 190501
I already know how to do this properly by using =FORMAT(2011-01-10,"YYYYMM")
But I am a bit curious to know how you do when you want to concatenate a date and a text string will this also show wrong result?
ELVIS_OF_BI
________ Information from ESET Smart Security, version of virus signature database 5785 (20110113) ________
The message was checked by ESET Smart Security.
http://www.eset.com
________ Information from ESET Smart Security, version of virus signature database 5785 (20110113) ________
The message was checked by ESET Smart Security.
Roger Govier Microsoft Excel MVP
.png)
