Datetime synchronization between Desktop and Service

[AndresEsteban]

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.

Power BI Service

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

[Martin_D]

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

[AndresEsteban]

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?

[Martin_D]

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}})

[AndresEsteban]

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