Date value is changed when visualized from a dataset

[MAFE62]

Hi guys,

I started to learn Power BI 2 months ago. I have been solving some problems looking for solutions in this community, however currently I have the following scenario and I haven't find a solution:

1.- I created a data model bringing data from Azure DevOps

2.- Then the data model is published in a Workspace in Power BI Services

3.- Based on that data model I created some reportes getting data using the option "Get Data / Power BI dataset"

The problem is that some date values are changed when I create a visual in the step 3. Aparently it is a random problem (I have not found a pattern or reason). Here you have the facts:

a) Raw data exported to Excel:  (look at Baseline/Actual Finish dates... those are the right dates coming from Devops)

b) If I create a visual using the data model (step 1), it looks OK:

c) However, if I create a visual using the data from the dataset (step 3), some values are changed!!!:

I'm using Power BI Desktop last version:

Your help is appreciated,

Martin Fernandez Marin

[danextian]

If I am not mistaken  PBI service is using GMT +0. I would convert Baseline Finish and Actual Finish columns to a GMT + 0 timezone then convert it to your local timezone. You can add a custom colum in Power Query using 

DateTimeZone.SwitchZone ( Datetime.AddZone ( [datetimecolumn], 0 ), yourtimezone )

If your colums aren't datetime, you may convert it to that format first. After adding the custom columns, you can switch the data type back to date only.

[MAFE62]

danextian,

The columns Baseline Finish and Actual Finish are datetime.

My question is: The conversion occurs even when the data comes from a field in a user table?

Thanks,

Mafe62

[danextian]

I'm thinking it could be because, in the backend, the database actually uses a different timezone. But once a report is exported, datetime fields are changed to the timezone of the user based on location or whatever was set by the user. I encountered this when working on Salesforce objects - time datetime info on the website and the report is different from that in the object.

[JarroVGIT]

It seems to me that you are victim of a 'feature' of Power BI Service. The PBI Service might use a different timezone then you are and you cannot change the timezone of the service. So, if you are pulling in a datetime of 1/1/2019 23:00:00 local time, it might be converted to the local timezone of PBI service when returned to you in PowerBI Desktop (when you connect to it as a dataset) to for example 2//2019 02:00:00. 

I can recommend this article for possible solutions:
https://radacad.com/solving-dax-time-zone-issue-in-power-bi

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[MAFE62]

djerro123:

Thanks for your comments.

I can understand the behavior of DAX functions like TODAY() or NOW() when are published in Power BI Service but in this case the date that is changed comes from a table (as a data), I mean, it is not the result of a buil-in function. I expected the data must be respected as it is (no changes at all).

Anyway, I will try your recommendation as a possible solution.

Best regards,

Mafe62

[stokidez]

Hi @MAFE62 - did you manage to fix your issue? I'm having exactly the same problem you describe.

Be good to hear if you resolved this - thanks, Dez.