Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Code for Last Refresh Date/Time (EST)

Hello All

 

I have the following m-code to show my last refresh date. When published in Power BI Service it shows 4 hours ahead (UTC). I need it to show -4 hours so that EST is reflected. Can anyone tell me how to modify the below to show the -4?

 

let
Source = DateTime.LocalNow(),
#"Converted to Table" = #table(1, {{Source}}),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DateTime"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "DateTime", "DateTime - Copy"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"DateTime - Copy", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Date", type date}}),
#"Duplicated Column1" = Table.DuplicateColumn(#"Changed Type", "Date", "Date - Copy"),
#"Changed Type1" = Table.TransformColumnTypes(#"Duplicated Column1",{{"Date - Copy", type time}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Date - Copy"}),
#"Duplicated Column2" = Table.DuplicateColumn(#"Removed Columns", "DateTime", "DateTime - Copy"),
#"Renamed Columns2" = Table.RenameColumns(#"Duplicated Column2",{{"DateTime - Copy", "Time"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Time", type time}})
in
#"Changed Type2"

Need Help

4 Replies

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    You need to use something along the lines of this, which will adjust the time of the report when it loads.

    DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-4)

    DateTime.LocalNow that you used is the system time, but the service is always at UTC time.
    You might want to check out this article I wrote Anonymous that will help you add a refresh time to your report using this methid, including a daylight savings offset - Add a Refresh Time Stamp To Your Power BI Reports — ehansalytics

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks----I changed everything to the following code showing -4 to account for EST. However when I published to the Power BI Service, it still changes it back to UTC time. Any thoughts on how I can resolve this, see below:

       

      let
      Source = DateTime.FixedLocalNow(),
      #"Converted to Table" = #table(1, {{Source}}),
      #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Last Refresh"}}),
      #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Last Refresh", type datetime}}),
      #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Last Refresh]), type date),
      #"Insert Time" = Table.AddColumn(#"Inserted Date", "Time", each DateTime.Time([Last Refresh]), type time),
      #"Added Custom" = Table.AddColumn(#"Insert Time", "Local Timezone", each DateTime.AddZone( [Last Refresh], -4), type datetimezone)
      in
      #"Added Custom"

      • edhans's avatar
        edhans
        Community Champion
        5 years ago

        You didn't use the timezone function. All you did was add a -4 timezone to your current time. 

        Go back and change the source to what I showed you earlier. It attaches a timezone at that time and the service properly interprets it once it is in DAX. It must be a datetimezone type - which you do have.

        I would get rid of ALL of that code - no need for 8-9 steps to get the time stamp on your report. And I would not include all of those other fields - Date, Time, etc. Those will come in wrong too depending on when the report refreshes - the day could be off a day for example since it is based on the refresh and not static dates from a sales table as an example. Once it is in DAX you can just format a measure to show Date or Time only. Or reconstruct a date that is properly TZ adjusted using the DATE() or TIME() functions.

         

        Go click on the article I showed you. It has 100% of what you need and it works. I've been using that for years. 

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    Hi Anonymous 

     

    You could create a calculated table with below codes. A calculated table is evaluated when it's created or the dataset is refreshed. After published to service, NOW() will always return a UTC datetime value. So we can minus 4/24 day from that to get a datetime value which is 4 hours behind UTC time.

     

    RefreshTable = {NOW() - 4/24}

     

     

    Or you could use below M codes. Substract 4 hours duration from the UTC time.

    let
    RefreshDateTime = DateTimeZone.FixedUtcNow() - #duration(0,4,0,0),
    #"Converted to Table" = #table(1, {{RefreshDateTime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Last Refresh Date Time"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Last Refresh Date Time", type datetimezone}})
in
    #"Changed Type"

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.