Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AndresEsteban
Helper I
Helper I

Datetime synchronization between Desktop and Service

I have the following problem:

My data source is a database that has timestamps in UTC+0.

When I make a report in Power BI Ddesktop it automatically puts it with UTC of my time zone (Spain), however, when I publish it in Power BI Service and update it, it is set to UTC0 so all my calculations are out of sync between power bi desktop and power bi service.

 

AndresEsteban_0-1713975468190.png

Power BI Service

 

AndresEsteban_1-1713975489715.png

 

Power BI Desktop

 

Is there any way to solve this so that when I update the data in Service the dates are not modified and the calculations match?

 

Thank you

4 REPLIES 4
Martin_D
Super User
Super User

Hi @AndresEsteban ,

If you know our users are all in same time zone as Spain, just add in Power Query the offset. If you want to see the correct time in both, Power BI Desktop and Power BI service, create a Power Query parameter to turn on/off offset, and keep it off in Desktop and turn it on by parameter setting for the pubished report.

I've provided a solution to calculate summer-/winter-time in Power Query in github: powerbi-solutions/utc-to-wintertime-or-summertime at main · MartinBubenheimer/powerbi-solutions (git...
Kind regards,
Martin

Thank you for your help @Martin_D but I have several questions:
1- What happens if my users are not in Spain?
2-How do I create this parameter that allows me to activate or deactivate the scrolling?
3-How do I create the scrolling parameter?

In the link that you provide me the file has nothing explained, could you help me please?

1-They see Spanish time with this approach. If you want to be dynamic in the local time, you can use Deneb custom visual. Deneb can access system time (of the user's computer) and DAX function TIME() gives you server time. This way you can calculate and add the offset locally. Here is some sample code how to access system time in Deneb visual: Clock Example | Vega

2- Parameters - Power Query | Microsoft Learn

3- No idea what difference to question 2 this is, but the link should explain everything

4- just copy and paste all queries excep "other queries" folder to your report. Then apply custom function fUtcZuDeutscheZeit to your datetime columns as needed using Power Query function Table.TransformColumns - PowerQuery M | Microsoft Learn The step should look like:

 

Table.TransformColumns(#"Previous Step",{{"Datetime Column", fUtcZuDeutscheZeit, type datetime}})

First, thank you very much for your help, the function works perfectly for me but I only have two additionals cuestions.

 

1-Is it normal that when applying this function, the data loading and update times in power bi service are affected and take much longer?

 

2-As I mentioned, the source of my data is a database whose datetime column is in datetime with time zone format, therefore, when I load the data into Power BI desktop it is loaded by default with my time zone.

 

Before applying the function:

  • Database data: 2024-03-29 13:38:14+00
  • Power BI Desktop by default with my timezone: 2024-03-29 14:38:14
  • Power BI Service after refresh UTC +0: 2024-03-29 13:38:14+00

Before applying the function:

  • Database data: 2024-03-29 13:38:14+00
  • Power BI Desktop by default with my timezone: 2024-03-29 14:38:14
  • Power BI Desktop new column with applying the function on the power bi desktop data: 2024-03-29 15:38:14
  • Power BI Service after refresh UTC +0: 2024-03-29 13:38:14+00
  • Power BI Service new column with applying the function UTC +1: 2024-03-29 14:38:14

Therfore with the function is correct because the correct datetime is 2024-03-29 14:38:14 UTC +1 that is my tiemzone

 

The problem is that how can I work on my Desktop with the original data and that the function is only applied when I publish the data. That is to say, that in desktop works with the data
2024-03-29 14:38:14 and not with the 2024-03-29 15:38:14 but that when I publish it, it takes the function to put the correct time zone?

 

Thank you

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.