Date Filters (Relative Date) UTC time restriction in Power BI

We have identified an issue where Power BI has a constraint when using a date filter.

When I filter by “Relative date”, and put “is in the last” and mark as “Include today” and choose the value “1” as shown below, after 9pm (Brazilian time), it marks as the next day of today’s date and not the today’s date (as it should be).

This is because the Power BI desktop or Power BI service uses UTC time as the default, and it is not possible to change it manually or leave any other predefined time. Therefore, since I am located in Brazil and it can happen with other users being located here or in another time zone too, this divergence will occur. When it is 9PM or 9PM here, the UTC time will already be 00:00 AM, and the date will be automatically changed to the next day.

If you want to know what the UTC time is now according to the time in your country, just access the link below.

UTC

In the example below, we can see that on my Windows system it was still 8/18/2020 but on Power BI it was already scheduled for 8/19/2020.

To resolve this problem, we can resolve it in a few ways.

Creating a DAX formula for those who connect directly to the database or use SSAS Tabular, but I will not show this example in this post because I do not use these connections and I prefer to have created everything in the database directly.

Another option is to create a table that has a single date with today’s date and will be updated daily.

And another option I found (in addition that I’m using the Multidimensional SSAS as a connection in this case), is to create a conditional field within an existing table. So, I already have a Date dimension in my DW (data warehouse), so I created a field called IC_DATAHOJE (portuguese language) which is the same as IC_TODAYSDATE, and I update this field daily to “YES” and “NO”, so I can use this field directly in Power BI filters.

In T-SQL language, what I did is:

    UPDATE D_GB_DATA
    SET IC_DATAHOJE = 'NAO';

    UPDATE D_GB_DATA
    SET IC_DATAHOJE = 'SIM'
    WHERE D_GB_DATA.DT_DATA = CAST(GETDATE() AS DATE);

In Oracle/PLSQL, what I did is:

    UPDATE D_GB_DATA
    SET IC_DATAHOJE = 'NAO';

    UPDATE D_GB_DATA
    SET IC_DATAHOJE = 'SIM'
    WHERE DT_DATA = TRUNC(SYSDATE);

So now we have the option automatically, which will be executed daily, to update this conditional field.

We can see in the image below on the Power BI filter bar that this option worked correctly. So by checking the option “YES” (“YES”), we will only have the current date.

That’s it! Now we can fix this Power BI restriction via database easily.