Forum Discussion

jderekc's avatar
jderekc
Helper IV
8 years ago
Solved

DAX Date Sorting (Again)

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

  • 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.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    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.