Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
76 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |