Forum Discussion

kevinsray's avatar
kevinsray
Helper II
4 years ago
Solved

PBIX and Service Date Difference

I have a report which has 2 measures: TODAYNUMBER = DAY(TODAY()) DayPercentage = DIVIDE('Date'[TODAYNUMBER],COUNTROWS('Date'))   When I look at the pbix file, the values are correct, however when...
Need Help
  • jeroendekk's avatar
    jeroendekk
    4 years ago

    kevinsray 
    The Power BI service is in UTC time always. (Yes very annoying when using time, especially when your +10.

    You could change your variable by adding 10 hours.

    VAR TODAYNR = DAY(NOW()+time(10,0,0))
    Now this off course would mean the Power BI desktop would be off by 10 hours the other way. Also this doesnt include any Day light saving changes which make it way more complicated.

    There are ways in Power Query to calculate your local time (or you could call an api like World Time API: Simple JSON/plain-text API to obtain the current time in, and related data about, a timezone.

    However these options (I would do the API) would only give you the date of the last refresh not the current date. Although these might be the some for your use case.

    Best regards,
    Jeroen

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!



     
     
     
  • jeroendekk's avatar
    jeroendekk
    4 years ago

    kevinsray 
    If you are using a lookup it this there has to be an exact match (so a time will not work) but you could covert the date to a date without time first.

    So 

     

    DayNo =
Var currentdate = DATEVALUE(NOW()+TIME(10,0,0,0))
RETURN
LOOKUPVALUE('Date'[DayNoOfYear],'Date'[Date],currentdate)

     


    Should work, but I don't have any date to check it on.
    Best regards,
    Jeroen

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!


jeroendekk's avatar
jeroendekk
Responsive Resident
4 years ago

kevinsray 
The Power BI service is in UTC time always. (Yes very annoying when using time, especially when your +10.

You could change your variable by adding 10 hours.

VAR TODAYNR = DAY(NOW()+time(10,0,0))
Now this off course would mean the Power BI desktop would be off by 10 hours the other way. Also this doesnt include any Day light saving changes which make it way more complicated.

There are ways in Power Query to calculate your local time (or you could call an api like World Time API: Simple JSON/plain-text API to obtain the current time in, and related data about, a timezone.

However these options (I would do the API) would only give you the date of the last refresh not the current date. Although these might be the some for your use case.

Best regards,
Jeroen

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!



 
 
 
kevinsray's avatar
kevinsray
Helper II
4 years ago

Any idea how I could do this for day of year also?
I have the following, but it is also out 10 hours.

DayNo = LOOKUPVALUE('Date'[DayNoOfYear],'Date'[Date],TODAY())
DayNoOfYear = DATEDIFF(STARTOFYEAR('Date'[Date]),'Date'[Date],DAY) + 1
  • jeroendekk's avatar
    jeroendekk
    Responsive Resident
    4 years ago

    kevinsray 
    If you are using a lookup it this there has to be an exact match (so a time will not work) but you could covert the date to a date without time first.

    So 

     

    DayNo =
Var currentdate = DATEVALUE(NOW()+TIME(10,0,0,0))
RETURN
LOOKUPVALUE('Date'[DayNoOfYear],'Date'[Date],currentdate)

     


    Should work, but I don't have any date to check it on.
    Best regards,
    Jeroen

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!


    • kevinsray's avatar
      kevinsray
      Helper II
      4 years ago

      Jeroen,
      You are da man. Thank you very much.
      There was one too many 0`s in the time thing, but after removing, it worked perfectly.
      Thank you so very much.