cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
flipcritic Frequent Visitor
Frequent Visitor

Date in Power BI Pro report is one day earlier compared to the Power BI Desktop report.

Apologies if this has been dealt with before. Searched for the topic but could not find it.

 

Our Desktop report has a date field that has been set (in Power Query) to local time (my location is in Australia) and displays correctly.

 

But once it is published online to the Power BI Pro service, the date displays one day earlier. Not sure how to rectify.

 

Desktop:

Capture.PNG

 

Pro:

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
flipcritic Frequent Visitor
Frequent Visitor

Re: Date in Power BI Pro report is one day earlier compared to the Power BI Desktop report.

Hi @v-frfei-msft / @bradsy 

 

DateTime.AddZone didn't work for me (works on the local PBI report file, but reverts back to UTC when published online).

 

DateTimeZone.SwitchZone did work for me. I added custom columns and used this (as cited here -> https://community.powerbi.com/t5/Desktop/Date-Field-Time-Zone-Fix/td-p/402509)

 

Hope MS finds away to allow users to view dates from their respective time zones in the future.

6 REPLIES 6
bradsy Regular Visitor
Regular Visitor

Re: Date in Power BI Pro report is one day earlier compared to the Power BI Desktop report.

This could be related to the service running on UTC time and your local time in Australia being 8 or more hours different.

flipcritic Frequent Visitor
Frequent Visitor

Re: Date in Power BI Pro report is one day earlier compared to the Power BI Desktop report.

That makes sense.

 

Can anyone from MS reply as to how to display the correct values? Adding another adjusted date field can be used as a workaround, but I would rather have this actually fixed.

Community Support Team
Community Support Team

Re: Date in Power BI Pro report is one day earlier compared to the Power BI Desktop report.

Hi @flipcritic,

 

Which function did you use in Power query? I made a test here using "DateTime.AddZone". Everything worked well here.

 

Capture.PNG

 

Here is the M code for your reference.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7LCcAwDIPhVYzPActqHk1XCdl/jaY9JIb/9oHQGEr4bfCVeHuAlSZ1nSkYxeu2HCwbihDbGKz8dp3NOPolOFiCNUMXcFsN1o0UnDNN53wB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type datetime}, {"value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each DateTime.AddZone([date],2)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type datetimezone}, {"date", type datetimezone}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Custom", type datetime}})
in
    #"Changed Type2"

Could you please share your code or pbix to me if possbile?

 

Regards,

Frank

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
bradsy Regular Visitor
Regular Visitor

Re: Date in Power BI Pro report is one day earlier compared to the Power BI Desktop report.

That works fine if you know the timezone. How does it work with users of one report in many timezones?

flipcritic Frequent Visitor
Frequent Visitor

Re: Date in Power BI Pro report is one day earlier compared to the Power BI Desktop report.

Hi Frank. Apologies for the late reply. Here is the code I'm using.

 

let
    Source = SharePoint.Tables("https://cloughaus.sharepoint.com/sites/WELIC/", [ApiVersion = 15]),
    #"46d74e66-b71d-42d2-aac1-df41faf9ba1a" = Source{[Id="46d74e66-b71d-42d2-aac1-df41faf9ba1a"]}[Items],
    #"Renamed Columns" = Table.RenameColumns(#"46d74e66-b71d-42d2-aac1-df41faf9ba1a",{{"ID", "ID.1"}}),
    #"Expanded FieldValuesAsText" = Table.ExpandRecordColumn(#"Renamed Columns", "FieldValuesAsText", {"Actions_x0020_Complete", "Actions_x0020_to_x0020_go", "Facility", "LINK", "MOC_x0020_Asignee", "Progress_x0020_Rate", "Progress_x0020_Rate_x003a_Progre", "Project_x0020_Title_x0020__x0028", "Risk_x0020_Level0"}, {"Actions_x0020_Complete", "Actions_x0020_to_x0020_go", "Facility.1", "LINK.1", "MOC_x0020_Asignee", "Progress_x0020_Rate", "Progress_x0020_Rate_x003a_Progre", "Project_x0020_Title_x0020__x0028", "Risk_x0020_Level0"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded FieldValuesAsText",{"Actions to go", "Actions Complete", "Project Title _x0028", "LINK"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Project", "CTR/Scope"}, {"Scope/Facility", "Description"}, {"Title", "MOC/NOTI Number"}, {"PriorityY", "Priority"}, {"Workpack", "Workpack Required"}, {"MOC Type", "Scope Type"}, {"Status of MOC", "Scope Status"}, {"Percent Complete", "Progress"}, {"Required Engineering_x0020", "Date Added"}, {"MOC Required by_x002", "MOC Due Date"}, {"Pending With WEL_x00", "Assignee"}, {"Action With_", "Completion Status"}, {"Completed or Recomme", "Date Closed"}, {"Associate Work Order", "WO Scheduled Date"}, {"WEL Assignee", "System Engineer"}, {"Deliverable required_x0020", "Delivery Required by"}, {"Current Delivery Dat", "Current Delivery Date"}, {"Actions_x0020_to_x0020_go", "Actions Summary"}, {"Actions_x0020_Complete", "Actions To Go"}, {"LINK.1", "LINK"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Delivery Required by", type datetimezone}, {"Current Delivery Date", type datetimezone}, {"Action Due Date", type datetimezone}, {"WO Scheduled Date", type datetimezone}, {"Date Closed", type datetimezone}, {"MOC Due Date", type datetimezone}, {"Date Added", type datetimezone}}),
    #"Calculated Local Time" = Table.TransformColumns(#"Changed Type",{{"Delivery Required by", DateTimeZone.ToLocal, type datetimezone}, {"Current Delivery Date", DateTimeZone.ToLocal, type datetimezone}, {"Action Due Date", DateTimeZone.ToLocal, type datetimezone}, {"WO Scheduled Date", DateTimeZone.ToLocal, type datetimezone}, {"Date Closed", DateTimeZone.ToLocal, type datetimezone}, {"MOC Due Date", DateTimeZone.ToLocal, type datetimezone}, {"Date Added", DateTimeZone.ToLocal, type datetimezone}}),
    #"Extracted Date" = Table.TransformColumns(#"Calculated Local Time",{{"Date Added", DateTime.Date, type date}, {"MOC Due Date", DateTime.Date, type date}, {"Date Closed", DateTime.Date, type date}, {"WO Scheduled Date", DateTime.Date, type date}, {"Action Due Date", DateTime.Date, type date}, {"Current Delivery Date", DateTime.Date, type date}, {"Delivery Required by", DateTime.Date, type date}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Date",{{"Work Pack Progress", type number}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type1", {{"Work Pack Progress", null}}),
    #"Replaced Errors1" = Table.ReplaceErrorValues(#"Replaced Errors", {{"Delivery Required by", null}}),
    #"Expanded FieldValuesAsHtml" = Table.ExpandRecordColumn(#"Replaced Errors1", "FieldValuesAsHtml", {"Actions_x0020_Complete"}, {"FieldValuesAsHtml.Actions_x0020_Complete"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded FieldValuesAsHtml",{{"FieldValuesAsHtml.Actions_x0020_Complete", "Actions To Go (HTML)"}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Progress", type number}}),
    #"Replaced Errors2" = Table.ReplaceErrorValues(#"Changed Type3", {{"Progress", null}}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Errors2", each [Date Closed] = null or [Date Closed] > #date(2017, 12, 31)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Completion Status] <> "Clough Scope Removed")),
    #"Changed Type4" = Table.TransformColumnTypes(#"Filtered Rows1",{{"Progress_x0020_Rate_x003a_Progre", type number}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type4", "Progress_x0020_Rate", "Progress_x0020_Rate - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column1", "Progress_x0020_Rate - Copy", Splitter.SplitTextByEachDelimiter({" - "}, QuoteStyle.Csv, false), {"Progress_x0020_Rate - Copy.1", "Progress_x0020_Rate - Copy.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Progress_x0020_Rate - Copy.1", type text}, {"Progress_x0020_Rate - Copy.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type2", "Progress_x0020_Rate - Copy.1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Progress_x0020_Rate - Copy.1.1", "Progress_x0020_Rate - Copy.1.2"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Progress_x0020_Rate - Copy.1.1", type text}, {"Progress_x0020_Rate - Copy.1.2", type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type5",{"Progress_x0020_Rate - Copy.1.1"})
in
    #"Removed Columns1"

The line with "Local Time" is where I have date values adjusted.

Highlighted
flipcritic Frequent Visitor
Frequent Visitor

Re: Date in Power BI Pro report is one day earlier compared to the Power BI Desktop report.

Hi @v-frfei-msft / @bradsy 

 

DateTime.AddZone didn't work for me (works on the local PBI report file, but reverts back to UTC when published online).

 

DateTimeZone.SwitchZone did work for me. I added custom columns and used this (as cited here -> https://community.powerbi.com/t5/Desktop/Date-Field-Time-Zone-Fix/td-p/402509)

 

Hope MS finds away to allow users to view dates from their respective time zones in the future.