Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
That woked. Thank you!