Inaccurate/Incorrect Month Appearing in Filter

[HanLee]

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 is originally in text form, "September 2017" but BI converts it into a date when I import the data so it's "1/9/2017". When using a filter, September is split into 1 September 2017 and 30 September 2017 and its the only month to do so. No other months are split like this. I've gone into edit queries and checked the date and there is no 30 September 2017 (only 1/xx/17) in there so I'm not sure what's happening. When I click on the filter and look at any of my other visuals, they're all blank as I'd expect, but why is it there then. There's no data associated with that

Could anyone offer any insight into this?

Cheers

Edit1: Looking at the filter, my option to select October is completely gone. When I put the month into a table, the October data is there! 

I've tried editing the data source and reselecting the data to reload it but this has not worked.

Edit2: File from either link: https://ufile.io/tomsa

https://drive.google.com/open?id=1iowO705IDY2vAgDLf3VVF8zJAJjB7hZX

I opened up another Power BI file and found one of my filters looks like the image below but only for October (other months look normal and only have the date not the time/time zone). If I select the filter and click another month, October completely disappears from the filter. This occurs when I update my Power BI every month and open a file made in a previous version.

 

[v-xjiin-msft · Accepted solution]

@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.

[SJ354]

@v-xjiin-msft

Hi Xi Jin,

I am experiencing the same issue since January update. Initially, the issue was limited to Power BI Desktop, so was waiting for it to get sorted by next update. But, I am still experiencing it after March update as well. Moreover, now my clients are also experiencing this issue on Power BI Service, however, everything is good at my end(Power BI Service). I am able to see "October 2017" in my slicers and filters.

Would really appreciate if you could please let me know why we are experiencing this issue and the time line when this will be sorted by Microsoft.

Thanks,

SJ

[HanLee]

He is not a dev so probably cannot give you a time window for this to be fixed. I've tried looking on the forum to answers to this question but I was unable to find any information about it.

MS may be unaware of the issue right now so it may not be fixed for some time. It doesn't look like all users are experiencing the same problem we are.

[SJ354]

Thanks for the information. What should we do to make them aware of this issue. Do you have any email ID or something on which we can post our issue.

Thanks,

SJ

[HanLee]

Hi SJ

You can post here

https://ideas.powerbi.com/forums/265200-power-bi-ideas

I'm not sure if this is the correct place but better than nothing. 

Cheers,

Han

[HanLee]

@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.

[v-xjiin-msft]

@HanLee

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.

[HanLee]

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]

@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]

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]

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. 

[Anonymous]

Please share the data file 

[HanLee]

I've shared the data file above. 

If I create a new report using the file it is fine, but creating the report again is very time consuming so I would rather not do it again

[Anonymous]

Not able to access the file..could you upload to google drive

[HanLee]

I have updated the link with a googe drive link

[Anonymous]

I pulled out the month to the filter and this what am seeing

[HanLee]

This is currently what I'm getting. If I create a new report then it reverts to normal but that is very time consuming. So I was wondering if anyone else has encoutnered this issue before