I see this question pop up periodically, I thought I’d update the blog with an older LinkedIn post I shared two months ago. In this post I share the M and DAX work around that has done the trick to get a date and time stamp posted on report pages. The added benefit is that it accounts for the users time zone. There is a download below that will let you paste the M directly into a blank table to get you started. Hope you enjoy!
Need a simple refresh time stamp displayed on a report page? I was surprised to run into multiple display issues in the service including incorrect time zones, changing date time formats, and even broken card visuals etc.
I need to give credit to Ruth Pozuelo Martinez. Her YouTube videos got me rolling with time functions in Power Query. Also, big thanks to Jay Sumners who reached out on my original post and gave me some key suggestions on using time zone offsets and auto adjusting for DST.
1. Power Query:
The M code (in the download below) creates a single row table that uses DateTimeZone.UtcNow() to pull the current UTC. UTC.UTCtime when the query is ran.
2. Add a UTC offset column in your Location (or employee) table:
Next I created new numeric column that simply identifies a UTC timezone offset for each location (or employee) depending on the defined time zone. For instance, Washington State locations are set to -7, while Utah are -6. For extra credit, you could even write a DAX variable in your measure that checks and auto adjusts for daylight savings!
3. Little help from DAX:
If you drive time zone from your location or branch table, and your manager covers multiple time zones, you might consider using a little DAX (MIN or MAX) to choose between the furthest West or East time zone. We use the MIN of our time zone offset as we are based on the West coast.
DAX brings this all together by calculating two variables. One for the MIN() of the date/time in the query table. Then another for the MIN (or MAX) of the visible location offsets.
Add it all together, and with some help from row level security that isolates (and defines) a location or employee record (and now the offset column), you should end up with a card visual that returns the correct time zone for the user.
Hope this helps, and have a great week!
Author: Mark Walter – StoryBI.com
Leave a Reply