"Today" seems like such a simple term, it should be clear what we mean when we say today - right? As I write this it's Jan 13, 2022 in Auckland, New Zealand, but still Jan 12, 2022 in most of Europe and North America. So when we publish reports to PowerBI.com, what date does it use for today?
Power BI Scheduled Refresh doesn't pull through today's data
Power BI uses UTC as its date value when determining "today". UTC does not change for daylight savings time, so New Zealand time zone is UTC + 12:00 (standard time) or UTC + 13:00 (daylight savings time). Having a universal standard time like UTC is handy, but only if we know how to use it and remember convert from our local time zones.
I've just had a client question why their Power BI report isn't pulling through the latest day's data. They were using a Date table with an end date of DateTime.LocalNow(), which works beautifully in Power BI desktop and uses the time on your local machine. However, in PowerBI.com Date.Time.LocalNow() returns UTC time.
Power BI Desktop Refresh
In the screenshot below, you can see the result of doing a refresh at 11:14am on January 13, 2022 NZ time. This is 10:14pm on January 12, 2022 UTC time time:
Power BI Service (PowerBI.com) Refresh
In the screenshot below, you can see the result of doing a refresh at 11:13am on January 13, 2022 NZ time, but the DateTime.Local() function returns the wrong date - yesterday! DateTime.FixedLocalNow also returns the wrong date (at least for New Zealand). All functions return the UTC date and time, not the local time here in NZ like we'd want to see.
How to ensure Power BI refreshes at the correct date and time
Note the #duration() function accepts 4 arguments: days, hours, minutes, seconds. I have used 12 as the second argument to this function since I'm in New Zealand Standard Time right now and that is 12 hours after UTC.
If you're wanting to compare dates, just make sure that all your dates have the correct time zone information. If you're pulling data from a SQL database it will likely already have this info. If you're generating a DimDate table from M query you'll need to tell Power BI the timezone for those dates:
= Table.AddColumn(#"Sorted Rows", "ShowDates", each if DateTime.AddZone( DateTime.From( [Date]) , 12 ) <= DateTimeZone.UtcNow() then 1 else 0)
The code above compares the [Date] column of my date table to the current time (in UTC). In order to make this comparison accurately, I must give Power BI additional information about which time zone I'm in. Thus I use the Date.Time.AddZone() function to add the 12 hours to get to NZ standard time.
