cancel
Showing results for 
Search instead for 
Did you mean: 

Simple Dataset Last Refresh Timestamp Workaround

188 Views
Highlighted
Mholsen Member
Member

Simple Dataset Last Refresh Timestamp Workaround

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 )
    )