Hey Digital Friends,

Making an automated label that would dynamically update with the latest date and time of the last refresh of my dashboards was always a challenge. Recently I came across a great way to accomplish this in Power BI. Download the example file at the github link below:

https://github.com/bthomasmoore/Power_BI_Examples.git

The solution is to create a table that has the last refreshed date and time of your data. Essentially every time you hit the “refresh” button in Power BI, this date/time field will update. Next, we have to create a calculated measure to pull the date/time value from the table. Then we can create another calculated measure that has the date/time field and any text we want to display on our label. Finally you can pop that label measure on a card visualization in Power BI and you have your fancy, dynamic date label.

3 Quick & Easy Steps:

  1. Open Power Query Editor and add a New Blank Query. Copy and paste the M code below to create a table. The DateTimeZone.LocalNow returns the date and time of the local server. If your server is not in your timezone then use the DateTimeZone.SwitchZone to increase or decrease the time. Don’t forget to change the data type to Date/Time/Zone if it is not already. Apply the changes and exit Power Query.
= #table(
     type table[Date Last Refreshed=datetime]
     ,{{DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-5,0)}})

2. Next, we are going to create a calculated measure that returns the date time value in our table that we just created. Notice that this is the only value in the table. This step is important because we need a way to retrieve the value in the table. We will use this measure in our final label measure. The VALUES() function let’s us accomplish this nicely.

Date Last Refresh = 
// This measure retrieves the last date refreshed value 
      VALUES('Date Last Refreshed'[Date Last Refreshed])

3. Finally, we will create our label calculated measure. You can add any text with spaces that you want to display “face up” to your users. I choose to go with Data Last Refreshed: for this example. Create the calculated measure with the DAX below:

Label Date Last Refresh = 
// This measure creates creates our date last refresh label
      CONCATENATE(
           "Data Last Refreshed: " 
           ,'Date Last Refreshed'[Date Last Refresh] 
) // End CONCATENATE

HOORAY! Now add a card visualization and drop your Label calculated measure in it! You can edit the card visual to change the apperance of the text anyway you like. Now you have a dynamic last refresh date label face up in your dashboards.

Give it a try with the example Power BI file and drop some comments below.

Stay Digital,

Ben

Leave a Reply

Processing...
Thank you! Your subscription has been confirmed. You'll hear from us soon.
Subscribe For More Content
Digital Pharmacist
ErrorHere