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
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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors