I’ve been playing about with PowerView and PowerPivot recently, while also getting used to the Analysis Services (SSAS) Tabular model in SQL Server 2012 (Denali). The tabular model provides a high-compression, in-memory store for easy data model construction and analysis. Ad-hoc calculated fields can be defined within PowerPivot (built on the same xVelocity technology) using Microsoft’s Data Analysis eXpressions language, which is very similar to Excel functions. However, DAX is still pretty basic in some places, and while looking for a simple Month Name function (similar to the GetMonthName function in .NET) I ran into a couple of interesting issues.
I’m into the habit of using integer keys for my date dimensions for storage and performance purposes, but this threw up a slight complication as opposed to using a raw date key when trying to extract date components (like Month name) from the integer.
Now, when I say issues, I make it sound like a bug, but the month formatting in DAX is actually similar to Excel which, I’m reliably informed, has a really screwed up date formatting functionality, in which months are identified by the number of days away from the start of the year, not the month number (1-12) itself. Imagine my surprise when I tried to pass in a month number of 9 and was rewarded with the month name “January” instead of “September”.
Unlike the .NET function, which is based on an integer between 1 and 12 representing the month, DAX (and Excel) seem to be day based (weirdly starting from the number 2 - if anyone knows why this is, I’d love to hear in the comments below). Therefore passing an integer of 9 to the FORMAT function with the “MMMM” format actually returns “January”, 33 gives you “February”, 70 gives you “March”, and so on. But, if you pass a date to the FORMAT function, rather than an integer, everything is handled internally and you don’t need to worry about the odd numbering scheme in DAX. So, converting my date integer to a numerical string, then to a date, I can pass that into the FORMAT function with the “MMMM” format and let DAX do all the work:
=FORMAT(DATE(LEFT(FORMAT([DateKey],"#"),4),RIGHT(LEFT(FORMAT([DateKey],"#"),6),2),RIGHT(FORMAT([DateKey],"#"),2)),"MMMM")
Nothing hugely complex in here but as someone who’s used to the .NET implementation based on the month number, this one had me stumped for a while (okay, it was the end of the day on a Monday, but still). So, if it saves anyone some time, I won’t have struggled for nothing.