Forum Discussion
Helper IIPBIX 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 viewing the same report, with freshly refreshed data on the web, they are showing 1 less day on the TODAYNUMBER and wrong DayPercentage value as TODAYNUMBER is wrong.
I am in Australia with timezone of +10. I am going to assume that this is the problem (???) but was wondering how to fix it.
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
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.
SoDayNo = 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!!
4 Replies
- jeroendekk
Responsive Resident
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!!
kevinsrayAny 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
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.
SoDayNo = 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!!