Relative Date filter is set to UTC, need to change to AEDT

[SusuYes]

Hello

I have relative date filters on some of my visuals. However, when I set it to filter for 'today' it shows yesterdays date until 11am in AEDT when the date changes in UTC timezone. 

I have created a calculated column to try and fix that: 

LocalDate = [Date]+#duration(0,13,00,0)

however that did not work, any ideas? 

[lq250052]

1. Create a Date Table with Relative Date column as CurrentDayOffset. For example, today is 0, yesterday is -1.

2. Connect your fact table and date table on date column.

3. Drag your CurrentDayOffset to filter panel.

4. If you always want to see past 7 days, then set your CurrentDayOffset as -7<=CurrentDayOffset <=0

Relative Date as a native date filter take UTC time, there is no way to bypass that.

[macmy034]

Yeah - We already use this method in several places. The problem is really our end users who specifically want the relative date slicer in their reports. I do stress as oftern as I can that its in UTC time, but they still want it - hence this thread.

[durack99]

There is no way by default to change this currently, however you can work around it by creating a flag that determines wehther it is in the range or not. 

In my case I needed the report to only show the past 7 days, but using a relative date time filter didnt work as the server's current date is 10 or 11 hours behind NSW AEDT time. SO rather than using the relative date filter, I created a calculated column on my table where if the date was in the range... then return a 1 otherwise return a 0. 

Step 1: Create a measure for the offset from UTC in hours so we know how much to offset UTC (the time of power bi service by):

NSWDaylightSavingsOffset(UTC+) = 
VAR currentYear_ =
    YEAR ( TODAY())
VAR FirstSundayOct_ =

    SWITCH(TRUE, 
        WEEKDAY(DATE(currentYear_, 10, 1), 2) = 7, DATE(currentYear_, 10, 1),
        WEEKDAY(DATE(currentYear_, 10, 2), 2) = 7, DATE(currentYear_, 10, 2),
        WEEKDAY(DATE(currentYear_, 10, 3), 2) = 7, DATE(currentYear_, 10, 3),
        WEEKDAY(DATE(currentYear_, 10, 4), 2) = 7, DATE(currentYear_, 10, 4),
        WEEKDAY(DATE(currentYear_, 10, 5), 2) = 7, DATE(currentYear_, 10, 5),
        WEEKDAY(DATE(currentYear_, 10, 6), 2) = 7, DATE(currentYear_, 10, 6),
        WEEKDAY(DATE(currentYear_, 10, 7), 2) = 7, DATE(currentYear_, 10, 7),
        BLANK()
    )
    
VAR FirstSundayApr_ =
    
    SWITCH(TRUE, 
        WEEKDAY(DATE(currentYear_, 4, 1), 2) = 7, DATE(currentYear_, 4, 1),
        WEEKDAY(DATE(currentYear_, 4, 2), 2) = 7, DATE(currentYear_, 4, 2),
        WEEKDAY(DATE(currentYear_, 4, 3), 2) = 7, DATE(currentYear_, 4, 3),
        WEEKDAY(DATE(currentYear_, 4, 4), 2) = 7, DATE(currentYear_, 4, 4),
        WEEKDAY(DATE(currentYear_, 4, 5), 2) = 7, DATE(currentYear_, 4, 5),
        WEEKDAY(DATE(currentYear_, 4, 6), 2) = 7, DATE(currentYear_, 4, 6),
        WEEKDAY(DATE(currentYear_, 4, 7), 2) = 7, DATE(currentYear_, 4, 7),
        BLANK()
    )
RETURN
    IF (
        TODAY() >= FirstSundayApr_
            && TODAY() < FirstSundayOct_, 
        10,
        11
    )

Step 2: Using the offset go back 7 days from the current AEST time

FilterCutOffDateTime = 
VAR SydneyTime = UTCNOW() + TIME([NSWDaylightSavingsOffset(UTC+)], 0, 0)
VAR SydneyTime7daysback  = SydneyTime -7
RETURN SydneyTime7daysback

Step 3: you can then use this measure in a calculated column where your date field is being stored, and filter the report based on that:

InDateRangeFlag = IF('Your Table'[DateTime] > [FilterCutOffDateTime], 1, 0)

Hope that helps 

[threw001]

Thanks for this..

I am also in AET timezone

Does this mean it is a rolling 7 days prior to Today's date?

Do you know a way of pulling back fixed dates for last week, i.e. if current local date is 27/06/2024...

I want to show all of last week 17/06/2024 - 23/06/2024 from 00:00 24/06/2024 to 23:59 30/06/2024 (will refresh multiple times a day every day during this period)

When the report refreshes on or after 00:00 01/07/2024 and on or before 07/07/2024 23:59, the data displayed should be for dates 24/06/2024 - 30/06/2024

[br92]

This and other UTC issues take up 10% of my entire mental capacity. Infuriating.

[macmy034]

Nothing better than having to convert everything over and it looks like in the desktop version, only for it to change once it hits the server 😄 

[SusuYes]

Crazy to me that this hasn't been addressed natively in PBI

[Anonymous]

@macmy034   having the exact same issue.   Trying to do a "today revenue" card visual using the relative date...but we are in US central time and at 6pm....it switches to show $0 because it thinks "my today" is actual UTC tomorrow...

So frustrating that microsoft has not come up with an easy solution for this after all of these years

[macmy034]

Post refers to relative date slicers on the entire report. If you live in a country with a + timezone you have to wait that many hours until the relative date switches.

PowerBi reports still use utc as their reference point for relative date slicers.

[macmy034]

2022 and this is still doing the same thing... Solution is to use a date offset within your calendar table - however this will not allow you to use "Relative date" slicing.

[Anonymous · Accepted solution]

Hi @SusuYes ,

According to the official document:

• The data type for the field in the slicer must be a date, and not the default of text. Otherwise, the relative options don't show up in the slicer.
• Data models in Power BI don't include time zone info. The models can store times, but there's no indication of the time zone they're in.
• The slicer and filter are always based on the time in UTC. If you set up a filter in a report and send it to a colleague in a different time zone, you both see the same data. Unless you are in the UTC time zone, you and your colleague must account for the time offset you experience.
• You can convert data captured in a local time zone to UTC using the Query Editor.

Refer to:

Handling Different Time Zones in Power BI / Power Query

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[fbluemke]

I don't see how the solution was accepted as it does not solve the problem of the relative filter slicer not being flexible to use a specific time zone as the reference point. I want to tell the slicer to use Eastern Standard Time as the reference point, take UTC convert it to EST and then base all the relative filters on that date/time.  This is a real issue as I am building a real time report and want to always show the last hour, what is happening is that no data is showing because the last hour is 4 hours in the future...

[SusuYes]

These did not work as when I do Today(), I get the correct time and date already but when I use the relative date filters, the date does not change until 11am AEDT which is 00:00 UTC. 

[VahidDM]

Check this as well:

https://docs.microsoft.com/en-us/power-bi/fundamentals/supported-languages-countries-regions#choose-...

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[VahidDM]

G'day,

Please check this link:

https://radacad.com/relative-date-slicer-for-your-local-time-zone-in-power-bi

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[fbluemke]

I no longer see the Radacad relative slicer in the app source in power bi, is this not available anymore?

[macmy034]

This is still an ongoing issue;

I am in the timezone + 10 (Brisbane, Australia) and we do not get to use "Relative date filters / slicers" until 10 Am in the morning since PowerBi uses UTC as its reference point for Date Slicers.

1 . In the example below you can see that it is the 13-10-2022, all my date and time is set correctly on my machine to Brisbane Australia and PowerBi is set to follow the local setup.

2. Today is set to be included, the 13th

3. The date ends at yesterday, because its not "Today" in UTC Dates yet.

4. When asking for a list of all available dates - PowerBi again omits the 13th.

The issue arrises when new data comes in, the reports refresh at 6:00 am, but if the report is using relative date filters - wont show up until after 10am.