Forum Discussion
Inaccurate/Incorrect Month Appearing in Filter
Thanks for sharing us your sample data. It helps a lot to repro your issue.
Then for this issue, the newer version of Power BI desktop seems like will detect the text value automatically. And if the values are like date data, it will convert to date type automatically. You can verify this by checking at Edit Queries side.
To resolve this issue, you can simply change the data type to text in above power query script in screenshot.
Thanks,
Xi Jin.
v-xjiin-msft I noticed that this is an option but then the months are not displayed in the correct order which is quite troublesome.
I went ahead and changed to text and then back to date to see if that would work but that was unsuccessful.
Another screen shot below which is what happens when I click on the filter with October displayed incorrectly. October is in there, but if I click another month, October disappears as if PowerBI has realised that data has been updated. October is also at the bottom which is odd as it should be in calendar order.
But it works in my report. Please refer:
Have you applied the changes for your report? Then for the order in the slicer, since the month column is a text column. So the order should be in text order not the calendar order which is like my above screenshot.
Also, it will be more helpful to troubleshoot your issue if you can share us your report file (pbix).
Thanks,
Xi Jin.
Hi Xi,
sorry about my late response. I like having the calendar order and want to have that back. It looks a bit odd to have them in alphabetical rather than by the month.
I cannot share the file due to privacy. Do you have any more ideas?
Cheers,
Han
You should know that the calendar order is supported for date type data. Since the values in month column is text type data. It is not possible to sort them with calendar order.
However there's a workaround. Please refer, see if it works for you:
Create a calculated column, in this column use DAX expression to convert Month Name to Month Number, then sort it with numeric order.
The expression is like:
Column = SWITCH ( TRUE (), SEARCH ( "January", Sheet1[Month], 1, 0 ) = 1, 1, SEARCH ( "February", Sheet1[Month], 1, 0 ) = 1, 2, SEARCH ( "March", Sheet1[Month], 1, 0 ) = 1, 3, SEARCH ( "April", Sheet1[Month], 1, 0 ) = 1, 4, SEARCH ( "May", Sheet1[Month], 1, 0 ) = 1, 5, SEARCH ( "June", Sheet1[Month], 1, 0 ) = 1, 6, SEARCH ( "July", Sheet1[Month], 1, 0 ) = 1, 7, SEARCH ( "August", Sheet1[Month], 1, 0 ) = 1, 8, SEARCH ( "September", Sheet1[Month], 1, 0 ) = 1, 9, SEARCH ( "October", Sheet1[Month], 1, 0 ) = 1, 10, SEARCH ( "November", Sheet1[Month], 1, 0 ) = 1, 11, SEARCH ( "December", Sheet1[Month], 1, 0 ) = 1, 12 )Thanks,
Xi Jin.Hi Xi, I thought about doing something similar, just creating a relationship and numbering the months, then ranking them based on the number but this seems like a bit of a hassle (adding more and more things when this should be able to work even after the update).
It's ok, I'll be using the text function instead, thanks for your help.