Showing latest Refresh Date as a DateTimeZone (DateTimeZoneOffset) format in a Card - Power BI

[Anonymous]

I have a Date table in which i have created a `DateTimeZone` column that will show the DateTime in the particular Timezone e.g. `1/1/1901 9:08::24 AM -05:00` or as `1/1/1901 9:08::24 AM UTC-5`.

I am using this column to show the latest Refresh Date in a Card in Power BI.

However, when i view the Date table in the Modelling tab, i see that the dates are shown as DateTime format and not as DateTimeZone format.

On further investigation, a DateTimeZone format is also not listed in the Format or Data Type dropdowns.

How do i show the Date as a DateTimeZone format in the Card? 

[v-deddai1-msft]

Hi @Anonymous ,

There is no datetime zone format in date/time type in power bi model view. The only way to show datetime zone in card visual is to change the type to text in query editor.

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

Best Regards,

Dedmon Dai

[Anonymous]

Thanks for your reply @v-deddai1-msft . I finally concatenated the `DateTimeZone` value with "UTC-5" text to show on card. However, the problem is, this date table is a dataflow. It is not updating when i pull it in PBI Desktop and refresh the dataset. If i go back to the dataflow and open the table and see, the column gets refreshed to the latest UTC-5 datetimezone. Is there a way to refresh it to the correct datetimezone UTC-5 value in the PBI Desktop dataset when i refresh the whole dataset?

[v-deddai1-msft]

Hi @Anonymous , 

You can refer to https://docs.microsoft.com/en-us/azure/devops/report/powerbi/add-last-refresh-time?view=azure-devops to add a refresh date in power bi desktop:

    #"Added Refresh Date" = Table.AddColumn(#"{tableid}_Table", "Refresh Date", 
        each DateTimeZone.FixedUtcNow(), type datetimezone)

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

Best Regards,

Dedmon Dai

[Anonymous]

this is for Azure. I am not a member of any project. Where do i add this `Refresh Date` column? Currently i have my `DateTimeZone` column added to the DateTable dataflow.

[v-deddai1-msft]

Hi @Anonymous ,

You want to get the refresh time when you click refresh option in power bi desktop, am I right? You can just create a new blank query :

let
    Source = DateTimeZone.FixedUtcNow(),
    #"Converted to Table" = #table(1, {{Source}})
in
    #"Converted to Table"

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

Best Regards,

Dedmon Dai

[Anonymous]

What i want is, it should automatically update in PBI Desktop without me having to click the Refresh button. The Dataflow is already refreshing online. But the PBI Desktop does not show the updated date.

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

As far as I know, it can't be updated automatically in power bi desktop without click the refresh button. Power bi desktop need to get the latest data from data source by click the refresh button, or it will use the data cached in the power bi desktop.

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

Best Regards,

Dedmon Dai