Tag: TimeZone

Adjusting the time zone in Power BI DAX – Quick Tip

Hi Folks,

Here is the use case, I was actually pulling data from Model Driven App. As I was testing my report and cross validating the counts with that of CRM, so, it is when I noted that the count of records getting mismatched exactly by the time zone difference and if you were any other time zone except UTC and using Date Diff functions, you may encounter this problem with Power BI which you should definitely handle.

The Power BI Service will be in UTC time zone. Actually, we can’t set the time zone neither in Power BI Desktop nor Power BI Service, we have to amend the time difference, that’s it.

Consider you have a measure which calculates the date difference…consider adding the time zone difference to your DAX measures or using Power Query.

While here I am using the below DAX, I have adjusted here in DAX as below.

While I was in Singapore time zone (SGT), I have added UTC + 8 using the TIME Function available in Power BI and there you go…

Now the numbers should match exactly with that in your DataSource i.e. Power. Apps.

If you want to do this in Power Query based your scenario…you can follow the article in references.

Hope this helps someone…

References:

Edit time zone in Power Query

Cheers,

PMDY

Change the Time Zone in Azure VM

Hi,

We recently had to spin up a new Azure VM to run some of our Batch jobs where we need to schedule them to run at specific times in Singapore Time Zone.

But surprisingly when when we VM is provisioned and started…we see the default time zone is set to UTC. Usually by default, every time this would be set to UTC.

In our case, we need to change it to SGT(Singapore Time Zone). The usual way of changing the time zone from Settings–> Time & Language was not possible as this is disabled in our case. But even though it is enabled and changed here, it would be reverted back to UTC when we allocate & deallocate the VM.

So you can use Power Shell Scripts to make this thing work for you. Use below to get all time zones.

Get-TimeZone -ListAvailable

If you want to filter the list, you can do so like

Get-TimeZone -ListAvailable | where ({$_.Id -like “Singapore*”})

Finally for setting the timezone, you can use this cmdlet

Set-TimeZone -Id “Singapore Standard Time”

Hope this helps…

Thank you.

Cheers,
PMDY