Power BI: (Workaround) Power BI Service only provides UTC-based relative date and time filters

If you’ve done much work with Power BI, you’ll know that relative dates can be really useful. But if you’ve tried to use them, you’d also know that they aren’t as useful as they might at first seem. You configure them in Power BI Desktop and they look great. You deploy them to the Power BI Service, and you have a problem.

Why?

Because Power BI Desktop uses your local timezone and the Power BI Service doesn’t. So if the dates and/or times you have in the data you’re analysing don’t happen to be UTC based (or you live in England), you’re fresh out of luck. If you go looking for today’s data, you might well be seeing yesterday’s data.

I can understand why the service might be UTC based, but if a company’s data is created in Melbourne, and the report is built in Melbourne, and it’s for Melbourne people to view, it makes no sense to introduce UTC into that situation.

I really hope that the Power BI Service lets us set a local timezone on a per-user basis soon.

Workaround

But until then, I need a workaround. Fortunately, for many relative dates, etc. it’s not that hard. I have a Date table that gets processed daily. If I’m using Analysis Services (that’s what I mostly do), I just have a process of that table happening first thing early in the morning.

And by having a rich Date table, I can make all sorts of filtering much easier. For example, I use our SDU_Tools.DateDimensionColumns function to return the columns that I need. It has the usual types of columns:

Date, Day Number, Day Name, Short Day Name, Month Name, Short Month Name, Month Number, Month Label, Year, Year Label, Day of Year, Fiscal Month Number, Fiscal Month Label, Fiscal Year, Fiscal Year Label, Day of Fiscal Year, ISO Week Number

But I also use our SDU_Tools.DateDimensionPeriodColumns function to enrich the table with:

Is Today, Is Yesterday, Is Tomorrow, Is Future, Is Working Day, Is Last Working Day, Is Next Working Day, Is Weekend, Is Same Month, Is Month to Date, Is Same Month Last Year, Is Month to Date Last Year, Is Same Calendar Year, Is Calendar Year to Date, Is Last Calendar Year, Is Last Calendar Year to Date, Is Same Fiscal Year, Is Same Fiscal Year to Date, Is Last Fiscal Year, Is Last Fiscal Year to Date

Then rather than having to do a whole lot of tricky DAX, it’s easy to just use those columns for the relative filtering. You can see it in action here:

For more info on our free SDU_Tools, look here.

2020-03-27