Forum Discussion

AndresEsteban's avatar
AndresEsteban
Helper I
2 years ago

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.

 

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

Need Help

4 Replies

  • Martin_D's avatar
    Martin_D
    Solution Sage
    2 years ago

    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 (github.com)
    Kind regards,
    Martin

    • AndresEsteban's avatar
      AndresEsteban
      Helper I
      2 years ago

      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's avatar
        Martin_D
        Solution Sage
        2 years ago

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