Forum Discussion

HanLee's avatar
HanLee
Helper I
8 years ago
Solved

Inaccurate/Incorrect Month Appearing in Filter

Hi guys,   Just had something odd happen. So I recently updated Power BI and noticed that it's done some odd things to my filter by adding an extra "month" labelled 30 September 2017.   My data i...
Need Help
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    HanLee

     

    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.

HanLee's avatar
HanLee
Helper I
8 years ago

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

v-xjiin-msft's avatar
v-xjiin-msft
Solution Sage
8 years ago

HanLee

 

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.

  • HanLee's avatar
    HanLee
    Helper I
    8 years ago

    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.

    • HanLee's avatar
      HanLee
      Helper I
      8 years ago

      Just had a slight breakthrough on this issue. This is done in another report and I changed the month from date to text.

      The above is the result which is a text format but it looks like a date. In the original file, it's all still "January 2017", "February 2017" etc.

      I suspect once I add 2018 dates it will no longer be in order of month, but year, then month. So the order will become 1/01/2017, 1/01/2018, 1/02/2017, 1/02/2018, etc. (d/mm/yyyy date format)

       

      For now I will live with this. I really thought it would revert back to the text format but I suppose there are some issues. In the report I originally was concerned about, when I changed from date to text, I got back all my text dates like you had Xi "January 2017" onward but in this one it did not happen.

      Very odd but not the unexpected.