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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors