Forum Discussion
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_DecklerCommunity 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.
- jderekcHelper IV
That woked. Thank you!