Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jengwt
Helper V
Helper V

Difficulties Setting Reports to US Central Time, Data Refresh Times

PBI Community, I have been having some trouble getting my published PBI reports to display the datetime of a data refresh in US Central Time. While I know that the refresh time is displayed in the dropdown of the center of the upper toolbar, our users do not appreciate this feature and require that we add cards with the last refresh time to our reports.

 

The problem I have had with this on published reports is that the refresh time on these cards always displays in UTC, not CST/CDT, and my users become conused. I have been trying a couple of different things to try and get these datetimes to "stay" in US Central Time once they are published to the server.

Below is my current attempt, which creates a single record in a custom table, listing the datetime at which the last refresh was begun. This works find in desktop, but it reverts to UTC when published to the server.

 

 

let
    Source = DateTimeZone.SwitchZone(DateTimeZone.LocalNow(), -5, 0),
    #"Converted to Table" = #table(1, {{Source}}),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "RefreshDateTime"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"RefreshDateTime", type datetime}})
in
    #"Changed Type"

 

 

This next screenshot is taken from the desktop version of the report. The top-left card correctly shows the datetime in CST yielded by the above M code. To its right is the time at which I took the screenshot, after the report had finished refreshing. Below them is a hard-coded string measure which I wrote for reference in the published version, should I forget:

Capture.PNG

I then republished the report, but got preoccupied with another project and did not return to check on it until about 1230 CST, around which time I refreshed the dataset:

Capture2.PNG

I thought it was odd that the time should be -5 instead of the correct -6, and so I ran it again at about 1330 CST, just to be sure that I didn't somehow lose track of an hour:

Capture3.PNG

I am writing this at about 1530 CST (2130 UTC). As you can see, there are some wonky things going on with the time zoning in this report.

Since, to my knowlege, there is no way to set a timezone in a report, how can I at least get this refresh card to display in CST/CDT in the server? And, yes, I would like it to change with daylight saving time.

Thanks!

1 ACCEPTED SOLUTION
jengwt
Helper V
Helper V

I forgot about this post for a while. I combined two techniques I found online; one was to calculate the DST start and end dates by using the year, and then adjusting a datetime by timezone by whether or not it falls between the DST dates.

By calling the resulting datetime in a measure to be displayed on your report, you can force PBI to display the actual refresh time on your report.

Again, MSFT has the convininent refresh time in the report-info dropdown at the top of the page, but stakeholders will be stakeholders ¯\_(ツ)_/¯ 

The only time I think this might not work might be New Year's eve / morning. I've never remembered to test.

 

let
  Source = Table.FromRows({{Date.Year(DateTime.LocalNow())}},{"Year"}),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Year", Int64.Type}}),
  #"DST Start Date" = Table.AddColumn(#"Changed Type", "DST StartDate", each Date.AddDays(#date([Year],3,1),Date.DayOfWeek(#date([Year],3,1), Day.Sunday)*-1+14)),
  #"DST End Date" = Table.AddColumn(#"DST Start Date", "DST EndDate", each Date.AddDays(#date([Year],11,1),Date.DayOfWeek(#date([Year],11,1), Day.Sunday)*-1+7)),
  #"Last Refreshed UTC" = Table.AddColumn(#"DST End Date", "Last Refreshed (UTC)", each DateTimeZone.RemoveZone(DateTimeZone.UtcNow())),
  #"Last Refreshed EST" = Table.AddColumn(#"Last Refreshed UTC", "Last Refreshed (EST)", each if DateTime.Date([#"Last Refreshed (UTC)"]) > [DST StartDate] and DateTime.Date([#"Last Refreshed (UTC)"]) < [DST EndDate] then [#"Last Refreshed (UTC)"] - #duration(0,4,0,0) else [#"Last Refreshed (UTC)"] - #duration(0,5,0,0)),
  #"Last Refreshed" = Table.AddColumn(#"Last Refreshed EST", "Last Refreshed", each "Dataset last refreshed: " & DateTime.ToText([#"Last Refreshed (EST)"]))
  //#"Changed column type" = Table.TransformColumnTypes(#"Last Refreshed", {{"DST StartDate", type date}, {"DST EndDate", type date}, {"Last Refreshed (UTC)", type datetime}, {"Last Refreshed", type datetime}}),
  // #"Changed column type" = Table.TransformColumnTypes(#"Last Refreshed", {{"DST StartDate", type text}, {"DST EndDate", type text}, {"Last Refreshed (UTC)", type text}, {"Last Refreshed", type text}}),
  //#"Replace errors" = Table.ReplaceErrorValues(#"Changed column type", {{"DST StartDate", null}, {"DST EndDate", null}, {"Last Refreshed (UTC)", null}, {"Last Refreshed", null}})
in
  #"Last Refreshed"

 

The commented lines are just some other changes I made or modified over time, but don't currently use.

View solution in original post

4 REPLIES 4
jengwt
Helper V
Helper V

I forgot about this post for a while. I combined two techniques I found online; one was to calculate the DST start and end dates by using the year, and then adjusting a datetime by timezone by whether or not it falls between the DST dates.

By calling the resulting datetime in a measure to be displayed on your report, you can force PBI to display the actual refresh time on your report.

Again, MSFT has the convininent refresh time in the report-info dropdown at the top of the page, but stakeholders will be stakeholders ¯\_(ツ)_/¯ 

The only time I think this might not work might be New Year's eve / morning. I've never remembered to test.

 

let
  Source = Table.FromRows({{Date.Year(DateTime.LocalNow())}},{"Year"}),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Year", Int64.Type}}),
  #"DST Start Date" = Table.AddColumn(#"Changed Type", "DST StartDate", each Date.AddDays(#date([Year],3,1),Date.DayOfWeek(#date([Year],3,1), Day.Sunday)*-1+14)),
  #"DST End Date" = Table.AddColumn(#"DST Start Date", "DST EndDate", each Date.AddDays(#date([Year],11,1),Date.DayOfWeek(#date([Year],11,1), Day.Sunday)*-1+7)),
  #"Last Refreshed UTC" = Table.AddColumn(#"DST End Date", "Last Refreshed (UTC)", each DateTimeZone.RemoveZone(DateTimeZone.UtcNow())),
  #"Last Refreshed EST" = Table.AddColumn(#"Last Refreshed UTC", "Last Refreshed (EST)", each if DateTime.Date([#"Last Refreshed (UTC)"]) > [DST StartDate] and DateTime.Date([#"Last Refreshed (UTC)"]) < [DST EndDate] then [#"Last Refreshed (UTC)"] - #duration(0,4,0,0) else [#"Last Refreshed (UTC)"] - #duration(0,5,0,0)),
  #"Last Refreshed" = Table.AddColumn(#"Last Refreshed EST", "Last Refreshed", each "Dataset last refreshed: " & DateTime.ToText([#"Last Refreshed (EST)"]))
  //#"Changed column type" = Table.TransformColumnTypes(#"Last Refreshed", {{"DST StartDate", type date}, {"DST EndDate", type date}, {"Last Refreshed (UTC)", type datetime}, {"Last Refreshed", type datetime}}),
  // #"Changed column type" = Table.TransformColumnTypes(#"Last Refreshed", {{"DST StartDate", type text}, {"DST EndDate", type text}, {"Last Refreshed (UTC)", type text}, {"Last Refreshed", type text}}),
  //#"Replace errors" = Table.ReplaceErrorValues(#"Changed column type", {{"DST StartDate", null}, {"DST EndDate", null}, {"Last Refreshed (UTC)", null}, {"Last Refreshed", null}})
in
  #"Last Refreshed"

 

The commented lines are just some other changes I made or modified over time, but don't currently use.

jengwt
Helper V
Helper V

MSFT people: stop marking posts as solutions. We still don't have a dynamic way to account for DST without doing things like importing data from random websites. There needs to be a dynamic datetime function which does this.

lbendlin
Super User
Super User

Radacad has a couple of articles about this

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

Thanks, but their Method 2 is the one I'm using and, as reported, it doesn't work in published reports.

However, I think this will do the trick:

 

Source = DateTimeZone.SwitchZone(DateTimeZone.LocalNow(), -6) - #duration(0, 6, 0, 0)

 

or

 

Source = DateTimeZone.LocalNow() - #duration(0, 6, 0, 0)

 

I'm going to confirm if having the extra functions, as in the first code, makes a difference.

Except it does not adjust for DST. That is another problem we'll have to tackle. Sounds like a good future feature (hint, hint, MSFT)!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors