PowerPivot DAX Session Notes 1 – Dates

Part of my preperation for TechEd Africa this year has required me to really get into PowerPivot and DAX, and I thought I’d share some of the lessons learnt along the way…
 
Dates
 
So, in standard Analysis Services models, the concept of a Date dimension is quite accepted… and the world of PowerPivot is not much different.
Part of creating this type of Dimension / Lookup table involves adding attributes like ‘Month’, ‘Year’, ‘Day of Week’ to a given date. Given the date expressions in Excel, it is quite straight forward to add these attributes using these standard Excel functions.
 
One that I didn’t find out the box though was a MonthName(<date>) function.
So, approaches to getting this right are:
1. Using a massive switch / case statement; yuck 🙂
2. Using a lookup table of sorts to map MonthNumber to MonthName; less yuck.
3. Use Excel date formatting features to achieve the same result… (and yes, I only learnt about this this morning, always more to learn)
 – =TEXT([@Date],”mmmm”)
 
Another common hinderance is the sort order of months.
By default sorting is alphabetical, meaning April suddenly appears before February. (and oddly enough this does confuse end users!)
 
 
Again, the more SQL based approaches would involve injecting some kind of number into the month name and sorting accordingly. (But 02-February is also not so user friendly on reports!)
Fortunately, Standard Excel sorting functions understand Months. So, if you sort the months A-Z, Excel will sort correctly.
 
 
Advertisements

, ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: