Incorrect dates displaying in desktop, but web is fine when published

[rcampbell_ljh]

Hi All

I have an issue with a date displaying incorrectly in the desktop version of PBI, but when I publish to web the date is correct. 

the problem date is 1st October 2017, displaying in desktop as 30 September 2017. 

Data source is MSSQL

In MSSQL Table = date is '2017-10-01'

in PBI Desktop - displayed as '30 September 2017'

in PBI Web - displayed as '1 October 2017'

There are no existing data transformations on this field, simply a format output to 'dd mmmmmm, yyyy'. This problem exists for all date format outputs. 

I would appreciate any advice on what is going on? Anything I am missing?

[guibenassi]

I am having the same issue, I have already open a ticket in Microsoft.

In my case the dates is 01/11/2020 and 31/10/2020.

In power BI desktop all the dates 01/11/2020 switch to 31/10/2020, when I publish on the power BI Online the report works fine.

[v-jianhe-msft]

Hi,

So you mean the date from the original data source should be “2017-10-01”, which is one day before in Power BI Desktop, but it is just fine in Power BI Service?

It seems I cannot reproduce the issue, if my datasource is “2017-10-01”, then the date in Power BI Desktop is “2017-10-01”, as well.

So are you using any date function in the report? Which makes the date to be dynamical, but not a static one? Coud you share more details on your design? 

BR,

Henry 

[rcampbell_ljh]

Thanks Henry

The date in original data source is "2017-10-01"

And displayed in PBI Desktop as "2017-09-30"

and displayed in BPI Web as "2017-10-01"

There are no date transformations or any other transformations on this field. If I change the date format to any other format is displaying as 30 September 2017    or   01/09/2017   or ... whatever other format I select.

It is very odd

[v-jianhe-msft]

Hi,

Thank you for your kind reply.

So what about importing the datasource again to the PowerBI desktop? And if you swith to another datasource and check, for example, using another type of datasource but with the same data, will the issue reproduce? 

Best Regards,

Henry 

[rcampbell_ljh]

Thank you for your prompt response. 

I have oulled the same data source in to a new PBI Project, which only contains the one single data source. 

Attached are 3 screen shots. 

1. Data Source as per MSSQL

2. PBI Desktop View

3. PBI Web View

I am making no data transformations on the way in to PBI, simply changing the date format. Regardless of the date format, the problem exists.

I have changed the PBI project to be "Direct Query" and "Import"  --> Same problem exists.

You will see from attached that the Data source has dates as '2017-10-01'.

When viewed in Desktop - date is displaying incorrectly as '2017-09-30'

and in Web view - date is displaying correctly as '2017-10-01'

Data Source in MSSQL = Date is '2017-10-01'When view in Desktop = Date is '2017-09-30'When view in Web = Date is '2017-10-01'

[ignatiusw]

I have the exact same issue as well at one of our client, just noticed it yesterday. I am using the latest Power BI desktop Version: 2.57.5068.501 64-bit (April 2018)

The 'incorrect' date appears to always be a Sunday and in either week 39 or week 40 depending on the year (might have to do with some years have 52 and others have 53 weeks). See attached imaged (highlighted).

[Ormesome]

Is there any chance the underlying data has a time component that is shifting particular records between days based on timezones?

[ignatiusw]

No, the CalendarDate column is date only, no time whatsoever (or all is 00:00:00), unless.... that's when the Daylight Savings Time kicks in, which it is for some of the more recent years but not for for the old years.

So I brought in the time component, all 00:00:00, and voila, at the date where it's shown wrong, the clock has been winded back 1 hour (from 12:00 AM to 11:00PM) and hence why the date is shown as previous date. And I've used the datetimeoffset for the CalendarDate data type, with UTC timezone (+00:00). If I look at April, sure enough it winds the clock forward 1 hour.

[Ormesome]

Does it make a difference whether the field is a Date/Time or a Date? I'm hoping that telling the Data Model that it's a Date will cause it to ignore any time shifting.

[ignatiusw]

even with data type = Date, it is still shifting the hour.

[Ormesome]

According to the office help

"Date and time formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified in Control Panel. Formats without an asterisk are not affected by Control Panel settings."

Maybe you've got an asterisk date selected?

[ignatiusw]

I've just tried other date formats without asterisks in front, it's the same incorrect date coming up 😞

[Ormesome]

I don't have the solution but I will continue to mull over the issue. At least we know what the problem is.

[ignatiusw]

It also affected the online version. Not sure why the OP didn't have the same issue on his/her online version.

[Ormesome]

I can't replicate the problem, but this thread (https://community.powerbi.com/t5/Desktop/Converting-Date-Time-to-Local-Time-considering-Daylight-sav...) indicates that there is a date/time/timezone datatype. Maybe this is being applied to your data somehow?

Would you try a calculated column and let me know how it goes?

__trueDate = DATEVALUE([date_with_dts])

I typically use this to strip the time component from fields.

[ignatiusw]

The same issue is still showing up using the calculated column you suggested.

It may have to do with regional setting, just not sure where, as it's online version. I can understand desktop version may be impacted by region setting. One of my director in US doesn't have this issue, both desktop and online. I'm in Australia.

I'll try to put the timezone (which I think should solve it) information into the date calendar over the weekend to see what happened.

[mike_honey]

One of my clients bought this to my attention and I was quickly able to repo it.  I'm sure it's a bug.

Please review my repo added to this Issue report and if you agree then vote it up and add your comments to confirm, and highlight it's importance to the Product Team.

https://community.powerbi.com/t5/Issues/Bug-October-1/idc-p/400669

[indhu]

I have the same issue. Any update? 

Thanks,

Indhu