DAX Date Sorting (Again)

[jderekc]

I know this question must have been asked and answered a million times, and believe me, I've looked through them.  However, I can't get this to work.  Basically I have a slicer that will show the past four months of data and the slicer needs to show the month name.  As the old issue goes, it is sorted alphabetically...  I tried adding a date sort table for just the months but that didn't help.  I get an error 

Here is my query I am trying to use to create the slicer (the date type is text of course):

Date Ranges = IF(MONTH(Sales[Doc Date]) = 1, "January", IF(MONTH(Sales[Doc Date]) = 2, "February", 
IF(MONTH(Sales[Doc Date]) = 3, "March", IF(MONTH(Sales[Doc Date]) = 4, "April", 
IF(MONTH(Sales[Doc Date]) = 5, "May", IF(MONTH(Sales[Doc Date]) = 6, "June", 
IF(MONTH(Sales[Doc Date]) = 7, "July", IF(MONTH(Sales[Doc Date]) = 8, "August", 
IF(MONTH(Sales[Doc Date]) = 9, "September", IF(MONTH(Sales[Doc Date]) = 10, "October", 
IF(MONTH(Sales[Doc Date]) = 11, "November", IF(MONTH(Sales[Doc Date]) = 12, "December", 
"Error"))))))))))))

Here is a screenshot of my date sort table:

I tried sorting on both the month (data type set to text) and the sort column (data type set to whole numbers).  But I keep getting that error.  What am I missing?

PS:  It would be nice also to have the months in order as the year rolls over.  For instance, right now I may have December, November, October, and September.  But when January comes around, I'd like it to display as January, December, November, October.

Thanks!

Derek

[Greg_Deckler · Accepted solution]

Hmm, first, you should be able to replace:

Date Ranges = IF(MONTH(Sales[Doc Date]) = 1, "January", IF(MONTH(Sales[Doc Date]) = 2, "February", 
IF(MONTH(Sales[Doc Date]) = 3, "March", IF(MONTH(Sales[Doc Date]) = 4, "April", 
IF(MONTH(Sales[Doc Date]) = 5, "May", IF(MONTH(Sales[Doc Date]) = 6, "June", 
IF(MONTH(Sales[Doc Date]) = 7, "July", IF(MONTH(Sales[Doc Date]) = 8, "August", 
IF(MONTH(Sales[Doc Date]) = 9, "September", IF(MONTH(Sales[Doc Date]) = 10, "October", 
IF(MONTH(Sales[Doc Date]) = 11, "November", IF(MONTH(Sales[Doc Date]) = 12, "December", 
"Error"))))))))))))

with

Date Ranges = FORMAT(Sales[Doc Date],"mmmm")

Then you need to relate your Month table to your fact table. Then you need to do a Sort By on your Month column in your Month table with your Sort column. Then you need to use your Month column from your Month table in your slicer.

[jderekc]

That woked.  Thank you!