A customer wanted to show the last dataset refresh date on a card in Power BI report.
The situation is as follows: the user created a report in PowerBI Desktop by connecting in import mode to a one drive folder and then published it to a Power BI workspace.
Scheduled refreshes work and show the refresh timestamp correctly in the PowerBI Service workspace view, but he would like to have a card that shows him the timestamp directly in the report.
The onedrive folder data source is the only one available and there is no relational database where to run a query like SELECT GETDATE().
Following Microsoft documentation, Add last refresh date to a Power BI report - Azure DevOps | Microsoft Docs, I suggest him to create a blank query:
= #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}})In order to have a small table with the timestamp that is evaluated only during the refresh process of the dataset.
The user creates the card from the LastRefresh table value and publishes the report (sample report):
So far so good.
But when refreshes (scheduled or manual) fires on the web service:
the time in the card is reduced by 2 hours.
This seems to be because Power BI servers are set to UTC as the default timezone.
The DateTime.LocalNow() function executed locally returns the time according to the Italian timezone, but when executed in Power BI service, it returns the timestamp according to the LocalNow() time of Power BI service, that is UTC.
Now, if we were in a country that does not observe daylight saving time, a correction like the following might be enough:
[LastRefresh]+#duration(0,2,0,0) Or:
DateTime.AddZone([LastRefresh],2) to force the time from UTC to local timezone (UTC+2:00).
However, since the local timezone varies during the periods of winter/summer time, which in turn change from year to year, this workaround does not work in this case.
In our case, as part of the European Central Time, the time standard is UTC+1:00 which becomes UTC+2:00 during the daylight savings time (DST), from 02:00 AM on the last Sunday in March, to 03:00 AM on the last Sunday in October (Source: https://time.is/it/CET).
Starting from the calendar dimension table we can create a calculated table (which we call 'tblDST_range') where, for each year, we have the starting and ending range of the daylight savings time period.
tblDST_range = SELECTCOLUMNS ( FILTER ( ADDCOLUMNS ( 'Calendar', "Start_date", MONTH ( 'Calendar'[Date] ) = 3 && WEEKDAY ( 'Calendar'[Date], 1 ) = 1 && DATEDIFF ( 'Calendar'[Date], EOMONTH ( 'Calendar'[Date], 0 ), DAY ) < 7, "End_date", MONTH ( 'Calendar'[Date] ) = 10 && WEEKDAY ( 'Calendar'[Date], 1 ) = 1 && DATEDIFF ( 'Calendar'[Date], EOMONTH ( 'Calendar'[Date], 0 ), DAY ) < 7 ), [Start_date] = TRUE || [End_date] = TRUE ), "DST", IF ( MONTH ( [Date] ) = 3, [Date] + TIME ( 2, 0, 0 ), [Date] + TIME ( 3, 0, 0 ) ) )
With this table, to the LastRefresh date (which we know will be computed in UTC) we will add 2 hours if it falls within the daylight savings time period of that specific year, otherwise 1 hour.
Let's adjust the last refresh time with this formula:
Adjusted_LastRefresh = VAR last_refresh = DISTINCT ( Lastrefresh[LastRefresh] ) VAR current_year = YEAR ( last_refresh) VAR start_period = --last sunday of march 2:00 AM CALCULATE ( MIN ( 'tblDST_range'[DST] ), YEAR ( 'tblDST_range'[DST] ) = current_year ) VAR end_period = --last sunday of october 3:00 AM CALCULATE ( MAX ( 'tblDST_range'[DST] ), YEAR ( 'tblDST_range'[DST] ) = current_year ) RETURN IF ( last_refresh >= start_period && last_refresh < end_period, last_refresh + TIME ( 2, 0, 0 ), last_refresh + TIME ( 1, 0, 0 ) )
In Power BI dekstop, it will appear misaligned, but once the report is published and updated, it will report the correct time.
This example is based on the European Central Time timezone, if your timezone is different you can change the start_date and end_date variables in 'tblDST_range' and the hours to add in [Adjusted_LastRefresh] according to your location.
Comments