Date Format Issues

[jake_ledger]

Hi all, 

I've tried having a good look through the forums and can't see to find an answer to the specific scenario I have so apologies in advanced if this has been covered before. 

This problem seemed to present itself yesterday at the changeover to a new month so I'm not sure if that helps with the context. 

I'm having an issue with the date pulling through from a Sharepoint Table into Power BI. The data in Sharepoint is Set to England UK formatting and the dates show up as England UK so DD/MM/YYYY. Screenshot below. 

This pulls into Power BI Desktop fine and I have a report which works with a relative date filter to show data from today which was working fine. I checked the date filter as a basic filter rather than relative and it shows up like this with the 1/12/2022 (DD/MM/YY) in the correct UK format. 

The issue I noticed however if that the online published version of the report was showing as that there was no date for that date. I checked as a basic filter again and for some reason it seems to be picking up the exact same data as an American format date so it's putting 1/12/2022 at the top of the list, as per below, as it seems to be treating it as American formant with (MM/DD/YYYY).

This has been working fine all the way through November, however yesterday this seemed to be the first time there was an issue so I'm not sure if this is something to do with the combination of dates and there being the possibility for it to be interpreted 2 ways, vs the original data having a day number which would have been too high for a month so there's only one option. 

Just to check again I've got PowerBI to export the dataset into Excel for me to look at, and you can clearly see that it's picking up the date I've shown in the screenshot right at the top, and decided that it's the other way around. The weirder bit is that it's changed all the dates to American format with MM/DD/YYYY, however some it's picking up everything else fine. The one thing I've noticed though is that the day number is clearly higher than could be interpreted as a month in the other data in the sharepoint table at the moment. 

I've tried checking in PowerBI to see if I'm missing a Region setting somewhere, and I can't find anything at all. Anyone got any suggestions for this as I can't keep going forward not being able to put any dates in earlier than the 13th of the month, and this is now stopping other users from accessing the web report with the relative date as it's just picking up nothing. 

[lbendlin · Accepted solution]

Is this column typed as text or as date?

[lbendlin · Accepted solution]

Is this column typed as text or as date?

[jake_ledger · Accepted solution]

Gone back to this again today and I didn't realise that you meant is the column set as Date and Time in PowerBI.

I presumed you meant from original source as in Sharepoint. Didn't realise that the data type wouldn't be carried through. 

So essentially this is the solution, updated the column Using Locale... to Date and Time in PowerBI and this has resolved the issue. 

[jake_ledger]

Type of information set to Date and Time

Date and Time Format set to Date & Time