Forum Discussion
Wrong date values after publishing report
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!
9 Replies
- saud968
Memorable Member
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!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.
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 LearnHope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- pallavi_r
Super User
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
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?
Satya2277Helper I
Anyone found the solution for this issue?
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
