Final Sprint 12 Tasks

With a few days left for Sprint 12 of the Power BI project, two lower priority items have been added from the project backlog and assigned to the team’s dataset designer:

A higher priority for this sprint (for the Dataset Designer) was to write an M query for the Products dimension that accounted for several issues with the current iteration of the SQL view. This task was addressed per last week’s blog. A separate blog post will feature the Power BI Template.

Last Refreshed Message

The end result of this work item should be a DAX measure that contains a text message advising of the dataset’s last refresh date. Power BI reports built off this dataset can use this measure in one or more report pages to advise users of the freshness of the data. A Card visual in a Power BI report will look like this:

The following is one of several possible designs and is exclusive to import mode datasets:

1. M query that refreshes and loads to the model with the current date
   • The table is hidden from the Fields list in the Report View
2. DAX measure retrieves the date value and concatenates the date with a string
   • A DAX variable is used to retrieve the date value from the table

A similar design for DirectQuery datasets is mentioned at the end.

Current Date M Query

The M query has one column, one row, and is both loaded to the model and included in each refresh:

The query simply computes the current date, builds a table from a record which contains that date, and assigns a date data type. (Right-click the query from the Query list or use the properties icon on the Home tab of the ribbon in the Query Editor to adjust the Enable load to report and Include in report refresh properties.)

I usually group tables loaded to the model which do not have relationships with other tables (and which are not security permissions tables) as ‘Parameter Tables’. Sometimes these tables store parameter values for users to select but other times they’re just used to organize DAX measures like display folders. Additionally, a hidden and blank ‘Measure Support’ table can be used to assign intermediate or ‘branching’ DAX expressions. These  measures aren’t exposed in the Fields list but are referenced by many other DAX measures and thus simplify their expressions. In summary, the parameter tables improve usability and manageability of the dataset. I write about them in the new book project.)  

Last Refreshed DAX Measure

The DAX measure, which itself is applicable to both import and DirectQuery models, simply retrieves the date into a variable and concatenates the variable with text:

You definitely could apply a different table or column name but in most scenarios the import dataset is refreshed daily and thus the table simply stores the current date. I assign the new measure to a common Home Table such as Adventure Works Sales to make it easy to find in the fields list.

Timezone Adjustment

Local time in MS Azure is in UTC. Therefore, a refresh at 8:10 AM in Boston, MA is reflected as 1:10 PM without an adjustment per the following image:

For a typical morning refresh the 5 hour difference won’t result in a different refresh date from a US Eastern perspective or for several other timezones. However, for a late evening refresh and for other parts of the world (or just to be more precise generally), you might build logic into your query to adjust the UTC time in Azure to your local time.

In the following revised M query, we adjust the UTC time reported by PBI/Azure to US Eastern Standard Time and then use the date of this datetime value to support the refresh message:

let
RefreshDateTime = DateTime.LocalNow(),
TimeZoneOffset = -5,
AdjustedRefreshDateTime = RefreshDateTime + #duration(0,TimeZoneOffset,0,0),
AdjustedRefreshDate = DateTime.Date(AdjustedRefreshDateTime),
TableCreate = Table.FromRecords({[CurrentDate = AdjustedRefreshDate]}),
DateType = Table.TransformColumnTypes(TableCreate,{“CurrentDate”, type date})
in
DateType

You might ask “What about daylight savings time (4 hours instead of 5)?” That also could be addressed in the query but it would very rarely impact the last refresh date and I don’t want to further complicate this blog post.

What about DirectQuery?

For DirectQuery, I also load a hidden table but use a Value.NativeQuery() M function with a standard SQL statement as follows:

Current_Timestamp() is an ANSI SQL function so it can be used with many databases. The datetime data type doesn’t impact the message from the DAX measure.