I created Date/Time dimension, but Month level members are ordered alpabetically.
Q: I created Date/Time dimension, but Month level members are ordered alpabetically. How do I fix that?
A: For each dimension attribute you can choose what do you use to order it by. You options are:
- By Key value.
- By Name value
- By another attribute value
Most likely your month level attribute is ordered by key or name, but key or name value is a string. That is why "April" month comes before "January". To resolve this issue you can create Month key that is numeric field, so ordering by it will return expected results.
I would recommend to create MonthKey value in the format YYYYMM so that it also contains year value in it. If you do not do that, you will have to use composite key (Year + Month) for key value of Month attribute. Then you make sure that you specify numeric key value for your month attribute and set to order attribute by key.
Best place to create your MonthKey is in your date/time dimension. If you do not want to change table, you can allways create named calculation in SSAS data source view.
Examples of formulas that you could use:
- CONVERT(int, LEFT(CONVERT(varchar, YourDate, 112), 6))
- Year(YourDate)*100 + Month(YourDate)