Forum Discussion

Maffimau's avatar
Maffimau
Frequent Visitor
9 years ago

Powerbi.com Server Time

Hi, I did a big report with a lot calculation based on now() function (hour, average time for a operation, ecc..).

After published the report I dicover that the database are in Irland -2 hours from Italy and all calulation are wrong. is there a system to syncorize my local time with the server time zone of powerbi so I can see the same time on desktop and on web? I can't change all the calculation on my report because I work with a direct query on sql server that read in realtime the hour and minute.

 

Thanks

Need Help

5 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi Maffimau,

     

    There is a known gap for the time zone displayed in Power BI Service. The time zone in the service is effectively always set to UTC.

     

    For example, if we use NOW() as a measure in Power BI Desktop, it can give the correct time based on your current time zone. However, once you publish to Power BI Service, the time will be adjust to UTC time.

    is there a system to syncorize my local time with the server time zone of powerbi so I can see the same time on desktop and on web? I can't change all the calculation on my report because I work with a direct query on sql server that read in realtime the hour and minute.

    Based on my test, you should be able to use the formula below to create a new measure for now calculation, and then replace NOW() function with this new created measure [NowFuntion] in all your related formulas. :smileyhappy:

    NowFunction =
TODAY ()
    + TIME ( HOUR ( NOW () ) - 2, MINUTE ( NOW () ), SECOND ( NOW () ) )

     

    Regards

    • Maffimau's avatar
      Maffimau
      Frequent Visitor
      9 years ago

      Thank you very much, I'll try asap. Now I have another big problem with the "BLANK" value:

      I have a column that often is total "EMPTY" with no value or "Empty" value. So when I try to count the column wint a measure the result of measure is "BLANK" does it mean that I can't have a "0" value if the colum is total empty.

      The problem is that if I set an alert in my dashboard with a certain value, the measure when is "empty" in the report on dashboard it not change value with "Blank", i can see only the last valid value of measure.

      I tried to convert in number but no "0" result, only "blank"; I tried to use the text column but when I have "blank" there is not a text to extract.

      Unfortunatly I have this colums that sometimes have 3-4 rows to count, and sometimes is total without rows or null value; this colum is a sql table that generate dates and delete the sames after some time.

      Can I "simulate" or "forced" a value "0" when I have no dates or without rows?

      Thanks

       

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi Maffimau,

        Can I "simulate" or "forced" a value "0" when I have no dates or without rows?

        Could you try adding "+ 0" to the end of the formula of your measure to see if it works? :smileyhappy:

        measure = COUNT(<column>) + 0

         

        Regards