UTC timezone issue

[zachjudge]

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:

(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):

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!

[v-kelly-msft · Accepted solution]

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!

[zachjudge]

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,

[v-kelly-msft]

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!