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 ...
Need Help
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

    • Maffimau's avatar
      Maffimau
      Frequent Visitor
      9 years ago

      Hi, thanks for the suggest but I tried and I can not do it because:

      I have a clumn that is a result from a sql tabel throw 5 filters! So if I use the function "count" the result is from the original table without filters; does it mean that the result have to be "1" (from the table with filters), but with "count" function the result is 96K.

      I don't know if there is a trick to "count" with few filters in the same function, and the filters come from interrogation to others table of sql.

      Thanks

      • dmvega's avatar
        dmvega
        Frequent Visitor
        9 years ago

        Does anyone know if this is going to be fixed? This is a problem becuase I use some views/page/report filters that uses the advanced filter option to see data from yesterday, but at 7pm local time the data in server shows no info because I am -5GMT and server is in UTC, and I have no idea how to fix this.

         

        Regards

         

        Diego