The Last Timestamp of Loading Data

Nolock · ‎08-07-2019

You know that, too. A user calls you and asks when a dataset has been updated for the last time. What do you answer?

I also had this problem but that is over now. And it will become only a memory for you, as well, if you read this article and implement the last timestamp of loading data into your dataset.

TLDR section

Visit my Github and download the PowerQuery query: https://github.com/nolockcz/PowerQuery/tree/master/The%20Last%20Timestamp%20of%20Loading%20Data
However, I personally recommend reading the article once before you use it in your project.

The long version

Let’s go through all possible solutions and let’s start with DAX. Neither DAX measure nor DAX calculated column are suitable for getting the last timestamp of loading data because they know nothing about when the data were loaded. A calculated column value is changed every time you open the PBIX file or you click on Refresh. And a measure is changed even when a visual using this measure is refreshed.

An example of a measure / calculated column: DAX Now = NOW()

When I publish a PBIX file with DAX Now to Power BI Service, the time is in UTC and not in my local time zone.

PowerQuery

Well, DAX is not a solution. The other option is PowerQuery. A PowerQuery query is called every time when our dataset is refreshed. That is a good start. A PowerQuery has a plenty of date, time, datetime, and datetimezone functions. There must be something what can show us the local timestamp at the moment of refreshing dataset. There is the function DateTime.LocalNow(). It returns a timestamp in the local time zone and it works fine if your PBIX file is refreshed only on your desktop. But if you publish it to the Power BI Service, you will see that the time is in UTC. No, not again. There must be something else.

Well, there isn’t. We have to write our own code for this purpose.

PowerQuery (online version)

The simplest solution is to use a webservice which responses with local time according to your time zone. There are plenty of them on the internet, I’ve used http://worldtimeapi.org/.

let
    // get current datetimezone from web API
    DateTimeApiResult = Json.Document(Web.Contents("http://worldtimeapi.org/api/timezone/Europe/Berlin")),

    // parse datetimezone timestamps from text
    UtcTimestamp = DateTimeZone.FromText(DateTimeApiResult[utc_datetime]),
    LocalTimestamp = DateTimeZone.FromText(DateTimeApiResult[datetime]), 

    Result = #table(
        type table
        [
            #"UTC timestamp" = datetime, 
            #"UTC date" = date,
            #"Local timestamp with offset" = datetimezone,
            #"Local timestamp without offset" = datetime
        ], 
        {
            {
            UtcTimestamp,
            DateTime.Date(UtcTimestamp),
            LocalTimestamp,
            DateTimeZone.RemoveZone(LocalTimestamp)
            }
        }
    )
in
    Result

But sometimes you don’t have an internet connection, or there is a policy that denies an access to the public internet. Then, we have to search for another solution.

PowerQuery (offline version)

The next solution will be an offline one. It is based on the UTC timestamp because it is the same wherever our dataset is refreshed.

The code is simple and if you think it is too long to read, don’t panic, half of it are comments 😉

let
    StandardOffset = #duration(0, 1, 0, 0),
    DaylightSavingTimeOffset = #duration(0, 2, 0, 0),

    // get start and end of daylight saving time
    // this code implements the rules of EU counties
    // if it doesn't fill your expectations, visit https://en.wikipedia.org/wiki/Daylight_saving_time_by_country and implement your own function
    fnDaylightSavingTimePeriod = (
        now as datetime
    ) as record => 
        let
            // the daylight saving time starts on the last Sunday of March at 1am UTC
            LastDayOfMarch = #date(Date.Year(now), 3, 31),
            StartOfDaylightSavingTime = Date.AddDays(LastDayOfMarch, -Date.DayOfWeek(LastDayOfMarch)) & #time(1, 0, 0),
            // the daylight saving time ends on the last Sunday in October at 1am UTC
            LastDayOfOctober = #date(Date.Year(now), 10, 31),
            EndOfDaylightSavingTime = Date.AddDays(LastDayOfOctober, -Date.DayOfWeek(LastDayOfOctober)) & #time(1, 0, 0)
        in
            [From = StartOfDaylightSavingTime, To = EndOfDaylightSavingTime],

    // get a timestamp in UTC (with offset 00:00 all year long)
    UtcNow = DateTimeZone.UtcNow(),
    // convert UTC datetime with offset to datetime
    UtcNowWithoutZone = DateTimeZone.RemoveZone(UtcNow),

    // get daylight saving time period
    DaylightSavingTimePeriod = fnDaylightSavingTimePeriod(UtcNowWithoutZone),

    // convert UTC time to the local time with respect to current offset
    LocalTimeWithOffset = 
        if UtcNowWithoutZone >= DaylightSavingTimePeriod[From] and UtcNowWithoutZone <= DaylightSavingTimePeriod[To] then
            DateTimeZone.SwitchZone(
                UtcNow, 
                Duration.Hours(DaylightSavingTimeOffset), 
                Duration.Minutes(DaylightSavingTimeOffset)
            )
        else
            DateTimeZone.SwitchZone(
                UtcNow, 
                Duration.Hours(StandardOffset), 
                Duration.Minutes(StandardOffset)
            ),
    
    // current date time without offset
    LocalTime = DateTimeZone.RemoveZone(LocalTimeWithOffset),

    // result table
    Result = #table(
        type table
        [
            #"UTC timestamp" = datetime, 
            #"UTC date" = date,
            #"Local timestamp with offset" = datetimezone,
            #"Local timestamp without offset" = datetime
        ], 
        {
            {
            UtcNowWithoutZone,
            DateTime.Date(UtcNowWithoutZone),
            LocalTimeWithOffset,
            LocalTime
            }
        }
    )
in
    Result

The only advanced thing is calculating the start and the end date of the DST (daylight saving time). The DST starts in EU on the last Sunday of March at 2 am and ends on the last Sunday of October at 3 am. If you have other rules in your country, you have to implement your own fnDaylightSavingTimePeriod function. This Wikipedia link https://en.wikipedia.org/wiki/Daylight_saving_time_by_country can help you do that.

The result is a table with one row which contains following columns:

#"UTC timestamp" = datetime, 
#"UTC date" = date,
#"Local timestamp with offset" = datetimezone,
#"Local timestamp without offset" = datetime

And a screenshot of the result table:

I always concentrate on the customer centricity and therefore my datasets do not only contain a well formatted timestamp of the last refresh, but also a text with a count of the days since the last refresh.

By the way, the brand-new Power BI Service design contains also a timestamp of the last refresh which is very handy 😊

However, you could say that the article has become obsolete because of the new Power BI Service design feature, I say it hasn’t. If you still need a timestamp of last refresh visible in your report, you don’t have any other option.

MCornish · ‎08-12-2019

Personally, I have a single SQL call with "SELECT GETDATE() AS LastUpdate".

This gets updated with the rest of the data.

Only really useful if you are already using a SQL connection

Anonymous · ‎08-14-2019

Hello
I might have multiple queries in my report's power query.

So, few of them might run for an hour or two.

Since all the queries refresh parallely, this query to pull the timestamp will complete well before my query which will run for 2 hours.

So, in this case, the timestamp I got is not entirely accurate, right?

Is there a way to make this timestamp query run at the end?

Regards

Nolock · ‎08-14-2019

Hi @Anonymous,

that is very interesting question. I don't display an exact timestamp in a report. For me it is enough to tell the user that the dataset was updated x days ago. It is then accurate enough for my usecase.

Back to your question. Unfortunately, it isn't possible to set an order in the PQ execution chain. Every query is an independent unit of execution. Therefore, we don't know when the refresh timestamp is set.

DeanWHall · ‎08-16-2019

HI @Anonymous ,

Whilst you cant control the order of execution of your PQ chain, you can 'fake' an order by feedint the above query an output from your longest runnning query - in effect forcing it to wait until completion as if it were dependent - then simply just dont use the input in your calculations.

Nolock · ‎01-23-2020

I have found out that the code doesn't work properly on the day of DST switch if your locale doesn't have Sunday as the first weekday.

The fix is simple, just tell the function Date.DayOfWeek, what the first day of week is: Date.DayOfWeek(LastDayOfMarch, Day.Sunday)

I've already fixed that on GitHub and here is the new code:

let
    /**********************************************************
        * 
        * Author: Michal Dvorak WITH(NOLOCK)
        * Since: 20.07.2019
        * Twitter: @nolockcz
        *
        * Source code: https://github.com/nolockcz/PowerQuery
        *
        *********************************************************/

    StandardOffset = #duration(0, 1, 0, 0),
    DaylightSavingTimeOffset = #duration(0, 2, 0, 0),

    // get start and end of daylight saving time
    // this code implements the rules of EU counties
    // if it doesn't fill your expectations, visit https://en.wikipedia.org/wiki/Daylight_saving_time_by_country and implement your own function
    fnDaylightSavingTimePeriod = (
        now as datetime
    ) as record => 
        let
            // the daylight saving time starts on the last Sunday of March at 1am UTC
            LastDayOfMarch = #date(Date.Year(now), 3, 31),
            StartOfDaylightSavingTime = Date.AddDays(LastDayOfMarch, -Date.DayOfWeek(LastDayOfMarch, Day.Sunday)) & #time(1, 0, 0),
            // the daylight saving time ends on the last Sunday in October at 1am UTC
            LastDayOfOctober = #date(Date.Year(now), 10, 31),
            EndOfDaylightSavingTime = Date.AddDays(LastDayOfOctober, -Date.DayOfWeek(LastDayOfOctober, Day.Sunday)) & #time(1, 0, 0)
        in
            [From = StartOfDaylightSavingTime, To = EndOfDaylightSavingTime],

    // get a timestamp in UTC (with offset 00:00 all year long)
    UtcNow = DateTimeZone.UtcNow(),
    // convert UTC datetime with offset to datetime
    UtcNowWithoutZone = DateTimeZone.RemoveZone(UtcNow),

    // get daylight saving time period
    DaylightSavingTimePeriod = fnDaylightSavingTimePeriod(UtcNowWithoutZone),

    // convert UTC time to the local time with respect to current offset
    LocalTimeWithOffset = 
        if UtcNowWithoutZone >= DaylightSavingTimePeriod[From] and UtcNowWithoutZone < DaylightSavingTimePeriod[To] then
            DateTimeZone.SwitchZone(
                UtcNow, 
                Duration.Hours(DaylightSavingTimeOffset), 
                Duration.Minutes(DaylightSavingTimeOffset)
            )
        else
            DateTimeZone.SwitchZone(
                UtcNow, 
                Duration.Hours(StandardOffset), 
                Duration.Minutes(StandardOffset)
            ),
    
    // current date time without offset
    LocalTime = DateTimeZone.RemoveZone(LocalTimeWithOffset),

    // result table
    Result = #table(
        type table
        [
            #"UTC timestamp" = datetime, 
            #"UTC date" = date,
            #"Local timestamp with offset" = datetimezone,
            #"Local timestamp without offset" = datetime
        ], 
        {
            {
            UtcNowWithoutZone,
            DateTime.Date(UtcNowWithoutZone),
            LocalTimeWithOffset,
            LocalTime
            }
        }
    )
in
    Result