show dataset last refresh date as per local timezone

[Anonymous]

Hi All,

In service the dataset refresh time is shown in UTC. I searched and found that it's bit complex to get the last refreshd date as per local time for different time zones. Either it needs to subtract so and so hours and minutes and to bring to UTC then convert to LocalTimeZone but that didn't worked for different timezones, worked for single timezone. Again this would not handle timezones where DST is observed for which it needs again maintainance of having DSTStartDate and DSTEndDate. Please let me know if there is simple way which can show last refresh date as per user timezone?

[v-xicai]

Hi @Anonymous ,

You may use an API, for more detail, you can refer to the link: https://www.excelguru.ca/blog/2016/06/08/display-last-refreshed-date-in-power-bi/.

Best Regards,

Amy

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

[Anonymous]

Hi there

I only had to worry about getting the last refreshed date in a single time zone but achieved this by making use of the free API offered by timezonedb.com.  You'll need to register yourself to get a key to enable use of the API.  After doing so, there is a simple request (get-time-zone) that can be issued to get the current time in the time zone of your choice.   All you need to do is set up a "Web" data source in the query editor and paste the URL to call the request.  The "formatted" column in the response contains the date/time that you want.

Here's a sample of the response message:

<?xml version="1.0" encoding="UTF-8"?>

<result>

<status>OK</status>

<message/>

<countryCode>AU</countryCode>

<countryName>Australia</countryName>

<zoneName>Australia/Sydney</zoneName>

<abbreviation>AEST</abbreviation>

<gmtOffset>36000</gmtOffset>

<dst>0</dst>

<zoneStart>1554566400</zoneStart>

<zoneEnd>1570291199</zoneEnd>

<nextAbbreviation>AEDT</nextAbbreviation>

<timestamp>1567070541</timestamp>

<formatted>2019-08-29 09:22:21</formatted>

</result>

Using this approach negates any need to mess about with UTC time and trying to handle daylight savings.

If you have multiple time zones to worry about, perhaps you could make multiple calls to the API and then devise a method of displaying the appropriate date to each user.  Alternatively, you could provide a report page that displays multiple date/times; each qualified by the time zone.

Hope this helps

[Anonymous]

@Anonymous  thanks for the solution. Report will be accessed by users in different timezone. I'm using DateTime.LocalNow() and showing it in card. So it gets updated whenever data set is refreshed. Can you please help me how do i have to set up card visual everyt ime to make call to API? I think giving table with UTC to different timezone would be better.

[Anonymous · Accepted solution]

@Anonymous  - I'm not sure if you misunderstood my comment about calling the API multiple times.  My suggestion was that you do this to store the last refreshed date for each time zone that you care about, e.g. LRD - UK, LRD - US EST, LRD - US PST, etc.  If you're able to access a "users" dataset that includes the default time zone of each of your users, you could use this to define a measure that selects the appropriate "LRD' value to display in the card.  Otherwise, you'd need to display multiple cards - one for each time zone.

Unfortunately, I don't believe there is any method of getting hold of the local time zone that is configured in the user's browser.  As you have probably gathered from reading various threads on this topic, Power BI does not currently provide proper support for time zone conversion; hence, one has to use somewhat clunky workarounds such as the one I have suggested.

[Anonymous]

@Anonymous thanks for clearing my doubt. I think using location from active directory will be good idea. Even though it will be effort but will be more accurate.