Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
11-08-2019 04:34 AM
Sometimes you want to show when the dataset was last updated, but having the timestamp show correctly on the Power BI Service is a nightmare - especially if you have users/viewers from different timezones.
An easy workaround is instead to show how long ago the dataset was refreshed in days, hours and minutes.
Often the user isn't really interested in the actual timestamp but wants to know how old the data is, which can be better shown in minutes or hours.
This is done by returning the UTC timestamp in a table when refreshing the dataset and then creating a measure that takes the difference between the current UTC time and the refresh UTC timestamp. Having both times in UTC ensures that the difference is always correct despite what timezone the viewer is in.
NB! The measures might be off in Power BI Desktop, but should always be correct on the Power BI Service.
The code for creating the 'Last Dataset Refresh' table:
let
Source = DateTimeZone.UtcNow(),
#"Converted to Table" = #table(1, {{Source}}),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Data Last Refreshed On"}})
in
#"Renamed Columns"
The code for creating the simple measure 'Minutes since last refresh' (uses ; instead of ,):
Minutes since last refresh = DATEDIFF(MAX('Last Dataset Refresh'[Data Last Refreshed On]);UTCNOW();MINUTE)
The code for creating the advanced measure 'Time Since Last Update' (uses ; instead of ,):
Time Since Last Update =
//CALCULATIONS
VAR DIFF = MAX('Last Dataset Refresh'[Data Last Refreshed On]) - UTCNOW()
VAR NumOfMinutes = DIFF * 24
* 60
VAR DAYS =
IF ( DIFF >= 1; INT ( DIFF ); BLANK () )
VAR HOURS =
INT ( ( DIFF - DAYS ) * 24 )
VAR MINUTES = NumOfMinutes
- ( DAYS * 24
* 60 )
- ( HOURS * 60 )
//TEXTS
VAR DaysText =
IF ( DAYS > 1; FORMAT ( DAYS; "00" ) & "days" )
VAR HoursText =
FORMAT ( HOURS; "00" ) & "hrs"
VAR MinutesText =
FORMAT ( MINUTES; "00" ) & "mins"
RETURN
IF (
NOT ( ISBLANK ( MAX('Last Dataset Refresh'[Data Last Refreshed On]) ) );
COMBINEVALUES ( " "; DaysText; HoursText; MinutesText )
)