cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zachjudge
Regular Visitor

UTC timezone issue

Hi,


I don't know if this is related to all the other timezone issues people have (none of the solutions I've found seem to work) because I don't think I can solve by having a local timezone slicer because of my same day last year DAX columns.

 

My timezone is +12 (NZ) and I have a previous day sales report that I had working by using filters on but my boss wanted to see same day LY which don't work (as far as I'm aware) with the filters.

 

Here's my issue, today is Tuesday and the report will show Sundays sales up until 12pm today and then will switch to Monday:

zachjudge_0-1623097252496.png

(Not all stores have been on the database for a full year)

 

It should look like this being that it is Tuesday today (screenshot is from desktop application):

zachjudge_1-1623097451607.png

 

This is because of the UTC server timezone.

 

This is the DAX query I have for yesterday sales:

 

Yesterday Sales = 
CALCULATE(
    SUM(fact_sales[sale_amt])
    , 'dim_date'[date_value] = TODAY()-1
    )

 

 

I have tried to run with today sales and just update the dataset at night but come 12pm, everything apart from same day ly goes (Blank) because it would roll onto the UTC today.

 

Thanks in advance to anyone that can help!

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi @zachjudge ,

You can use DateTime.AddZone function to achieve your requirement. This function adds the timezonehours as an offset to the input datetime value and returns a new datetimezone value.

DateTime.AddZone([Date],-12)

 

Reference
https://msdn.microsoft.com/en-us/library/mt253514.aspx

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

3 REPLIES 3
v-kelly-msft
Community Support
Community Support

Hi @zachjudge ,

You can use DateTime.AddZone function to achieve your requirement. This function adds the timezonehours as an offset to the input datetime value and returns a new datetimezone value.

DateTime.AddZone([Date],-12)

 

Reference
https://msdn.microsoft.com/en-us/library/mt253514.aspx

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

Hi Kelly,

 

How do I implement that because I feel like I've tried everything I can find on google that has this solution.

 

Cheers,

Hi @zachjudge ,

 

Sorry for the late reply.

I found a good example to address your issue:

https://powerbitalks.com/2020/08/convert-utc-into-local-time-zone-po.html

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors