Timezone difference between Desktop & Service

[richardmayo]

I have read a few of the other threads that deal with this, but cant really figure it out (perhaps my specifics are different)

I have a date table, which has multiple columns for identifiers such as "Current Week", "Current Month", etc.

I have created a dashboard in Power BI service which is a weekly dashboard. Problem is, im based in NZ, so we have to wait till 1pm our time, for the dashboard to register "current week", as the server is on UTC time.

Would the solution to my problem be as simple as adding 12 hours to the date colum in my date table?? 

Below is a snapshot of my date table so you get an idea on the "current week", etc

[GilbertQ]

Hi there

Yes that is exactly correct, you need to offset your Date for the timezone you are in.

This is so that when the dataset refreshes it will take the UTC Date then offset it by what you configured it to be. And then based on that it will update your date table.

I typically do this or base my date table off my data table.

[richardmayo]

Thanks @GilbertQ 

Sorry to be a noob, but can you share the DAX formula I would use?

Also, would it be a problem that some of my "current" columns use NOW() in the formula?

[GilbertQ]

Hi there

No worries happy to help

Just a quick question are you creating your date table in Power Query or in the data model?

Just so that I know which code to give you.

[richardmayo]

Hi, using data model in power bi deskto

thanks heaps

[GilbertQ]

Hi @richardmayo 

If you are using the UTCNOW() function this is how you can add in the Hours to offset it

Local Date = UTCNOW() + TIME(12,0,0)

The TIME Function works in Hours,Min,Sec

Here is the output from the above measure, you can use the same function above in your date table.

 

[richardmayo]

Hi @GilbertQ 

So the 'date' column in my date table is 

Date (Export) = CALENDAR(FIRSTDATE('rms repairs'[date_entered]),DATE(2022,03,31))

How would I add your part to that??

[GilbertQ]

Hi there

Try this and see if it works

Date (Export) = CALENDAR(FIRSTDATE('rms repairs'[date_entered]) + TIME(12,0,0),DATE(2022,03,31)+ TIME(12,0,0))

[richardmayo]

Hi @GilbertQ 

No, that didn't work, but I expect its because the field is in 'date' format and not 'date/time' format.

I dont really want to convert it to 'date/time' though, so is there anyway around it??

Thanks

[GilbertQ]

Hi there

What I would suggest doing then is to build your date table in Power Query (Which is where I recommend doing this, due to tremendous flexiblity)

When in the Power Query Editor you can have a Date/Time column to create the Offset. Then the final step can be converting it from a Date/Time to Date.

This will then solve your current challenge.

[richardmayo]

Hi @GilbertQ 

Building my date tables again sounds like a pretty big job.... is there a way I can take my current table into power query?

Thanks

[GilbertQ]

Hi there

Unfortunately not because they are different query languages.

Maybe my Date table could help?

https://www.fourmoo.com/2016/09/13/power-bi-how-to-easily-create-dynamic-date-tabledimension-with-fi...