Today() Function - UTC Offset

[AndySmith]

Hi 

Apologies in advance - as I have seen several posts on this already - but I am just struggling to make sense of any of it!

I have a measure which uses the 'today()' function to work out the previous day sales (with some added logic to show Fridays sales if it is the weekend or monday).

When I publish the report will not refresh until 11am (Melbourne Australia time) becuase of the time difference between the Power BI service UTC and my local time.

Can some please explain how I can account for this time difference. As far as I understand it is something to do with a time 'offset' function but this is where I am struggling to understand. 

I am using one fact table with sales data and a date table. 

Thanks

[v-rongtiep-msft]

Hi @AndySmith ,

"the time difference between the Power BI service UTC and my local time".

Below are 3 possible methods to get around this issue

• One method to get the correct DateTime is by connecting to a web service to return the local time of whatever region you need. 

• Probably the easiest method to use is by simply adding or subtracting the difference of your time zone and UTC time from the DAX NOW() function.

• Power Query has inbuilt functions to deal with this issue, namely the DateTimeZone functions. 

More details: Power BI Date and Time in Desktop vs Service – Bond Consulting Services

Best Regards
Community Support Team _ Polly

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

[AndySmith]

Thanks

I have tried to go with option 2! I have added 10hrs to the 'TODAY()' function.

Sadly this seems to return zero! Any ideas why this may be the case? 

[SG083]

I tried this and I found that the reason it returned 0 rows was because the field _Today is now set to the date + time so instead of being 11/04/2024 it's 11/04/2024 10:00:00.

So instead of just looking for records on 11/04/2024, it's looking for records where today's date = "11/04/2024 10:00:00".  If there are no records that fulfill this time criteria it will return 0.

I'm now looking into the Time Zone solutions.

[v-rongtiep-msft · Accepted solution]

Hi @AndySmith ,

Please try to use NOW().

Column = NOW()+TIME(10,0,0)

The column=today()+time(10,0,0) represents 2023/1/19 10:00:00.

Best Regards
Community Support Team _ Polly

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

[bolfri]

I am sorry, but I don't know the answer directly, but maybe this one might help:

https://support.helpshift.com/kb/article/how-do-i-set-my-timezone-in-power-bi/https://www.thepowerus...

Maybe creating a dummy_table with "today" column in Power Query M that respect your timezone and use in the dax would help?