Wrong date values after publishing report

[Numan_Dev]

Hi,

I have a report with 2 very important date columns. Unfortunately, when I upload this report to the Power BI service online, these date columns contain incorrect values. While in the desktop application I have “Start date: 01.01.2024” & “End date: 31.12.2024”, online I see “Start date: 31.12.2023” & “End date: 30.12.2024”, which also affects many other calculations. My system language and Poewrbi desktop are German. However, the display language in PowerBI Online and my browser are also set to German. I do not understand how the problem arises and would be very grateful for help! What is also strange is that after deleting and re-uploading the report, the data seems to be correct again, but then changes back to the wrong data after I look at it again a few hours later. I also tried changing the data types in the query editor from “date/time/time zone” to “date”, but this did not help. I also set a refresh schedule for the dataset, in which I tested whether it makes a difference if I change the time zone there, but it did not (I used both UTC Coordinated Universal Time and UTC +01:00 Amsterdam, Berlin...).

Would be very grateful about help! Thanks in advance!

[Satya2277]

Anyone found the solution for this issue?

[Numan_Dev]

Hi @Satya2277 ,

since it was one year ago, I am not sure if this was the solution, but maybe this helps:

In the Query Editor, create a column that converts the datimetzone columns to your specific timezone:

= Table.AddColumn(#"...", "date_local", each GetGermanTime(DateTime.AddZone([date], 0)), type datetimezone)

I created this "GetGermanTime" function to calculate the german timezone value, also considering the timezone changes between summer and winter time:
= (InputDateTime as nullable datetimezone) as nullable datetimezone =>
let
// Prüfen, ob der Eingabewert null ist
Result = if InputDateTime = null then
null
else
let
// Jahr aus dem Eingabewert extrahieren
CurrentYear = Date.Year(InputDateTime),
// Sommerzeit Start: Letzter Sonntag im März
StartDST = Date.AddDays(#date(CurrentYear, 3, 31), -(Date.DayOfWeek(#date(CurrentYear, 3, 31), Day.Sunday))),
StartDSTDateTime = #datetimezone(CurrentYear, Date.Month(StartDST), Date.Day(StartDST), 2, 0, 0, 0, 0),
// Sommerzeit Ende: Letzter Sonntag im Oktober
EndDST = Date.AddDays(#date(CurrentYear, 10, 31), -(Date.DayOfWeek(#date(CurrentYear, 10, 31), Day.Sunday))),
EndDSTDateTime = #datetimezone(CurrentYear, Date.Month(EndDST), Date.Day(EndDST), 2, 0, 0, 0, 0),
// Prüfen, ob Sommerzeit
IsDST = InputDateTime >= StartDSTDateTime and InputDateTime < EndDSTDateTime,
// Umwandlung in deutsche Zeit
GermanTime = if IsDST then DateTimeZone.SwitchZone(InputDateTime, 2) else DateTimeZone.SwitchZone(InputDateTime, 1)
in
GermanTime
in
Result

[pallavi_r]

Hi @Numan_Dev ,

This might be happening due to difference in Power BI server timezone and your local time zone.

Add a dax or power query step to incorporate the timezone difference in your calculation.

https://learn.microsoft.com/en-us/powerquery-m/datetimezone-switchzone

There's a good article posted by Reza Rad, you can check that implement.

https://radacad.com/solving-dax-time-zone-issue-in-power-bi

Let me know if you have any query. Happy to help.

If this post helps, please accept this as a solution. Appreciate your kudos.

Thanks,

Pallavi

[Numan_Dev]

Hi @pallavi_r,

thank you for your reply, I might try this out. But this solution sounds very strange to me, as I have set the datatype to "date" only. Shouldn't the dates be correct out of the box, instead of having the user to implement unnecessarily complicated logics to prevent this behavior?

[saud968]

Here are a few steps you can try to resolve this issue:

Check Regional Settings: Ensure that the regional settings in both Power BI Desktop and Power BI Service are consistent. Sometimes, discrepancies in date formats can arise from different regional settings.

Data Type Consistency: Verify that the date columns are consistently set to the "Date" data type in both Power BI Desktop and Power BI Service. You mentioned trying this, but double-checking might help.

Power Query Transformations: In Power Query, ensure that any transformations applied to the date columns are correctly set. Sometimes, transformations can cause unexpected changes when the report is published.

Service Settings: Check the settings in the Power BI Service, especially around time zone configurations. Ensure that the time zone settings are consistent with your expectations.

Refresh Schedule: Since you mentioned setting a refresh schedule, ensure that the data source itself is not causing the issue. Sometimes, the data source might have different date formats or time zones that affect the report after refresh.

Browser Cache: Clear your browser cache or try accessing the report from a different browser to see if the issue persists. Sometimes, cached data can cause display issues.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Numan_Dev]

Hi, @saud968,

Thank you for the reply! I couldn't find "regional settings" in power bi online. I only found settings for the display language, which is the same for power bi service and desktop. The datatype is set to date in Powerbi desktop. But why should be different in power bi service? I uploaded the report from the desktop app and also can't find any option to change the data model in the online service. The behavior is also the same in a different browser. 

[saud968]

Here are a few more detailed steps to help you troubleshoot:

Regional Settings in Power BI Service:

While there isn't a direct "regional settings" option, the language and locale settings can impact date formats. You can check these settings by going to Settings > General > Language in the Power BI Service1.
Verify Locale in Power Query:

In Power BI Desktop, open your report and go to Transform Data to open Power Query Editor.
Select the date columns and ensure that the locale is set correctly. You can do this by right-clicking the column, selecting Change Type > Using Locale, and then choosing the appropriate locale (e.g., German - Germany).
Check Browser Locale:

Ensure that your browser's locale settings match your desired date format. Sometimes, the browser's locale can override Power BI settings. You can usually find this in your browser's settings under language or content settings.
Data Source Time Zone:

Verify that the data source itself is not causing the issue. If the data source has a different time zone or date format, it might affect how dates are displayed after refresh.
Power BI Service Time Zone:

In the Power BI Service, check the time zone settings for your workspace. Go to Settings > Datasets > Scheduled Refresh and ensure the time zone is set correctly.
Re-Publish with Clear Cache:

Before re-publishing, clear the cache in Power BI Desktop by going to File > Options and settings > Options > Data Load and clearing the cache.
Re-publish the report and see if the issue persists.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Numan_Dev]

Hi, I checked all of these settings before but rechecked them. Everything is set to German. Also, I don't have a problem with the format but with the date values that change when I upload the report. 

[Anonymous]

Hi @Numan_Dev ,

You can check if your region settings are consistent by doing the following:
Add the locale in Power BI for Power BI visuals - Power BI | Microsoft Learn
Supported languages and countries/regions for Power BI - Power BI | Microsoft Learn

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.