cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kevinsray
Helper II
Helper II

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

2 ACCEPTED SOLUTIONS
jeroendekk
Resolver IV
Resolver IV

@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 ...

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!!



 
 
 

View solution in original post

@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!!


View solution in original post

4 REPLIES 4
jeroendekk
Resolver IV
Resolver IV

@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 ...

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!!



 
 
 

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

@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!!


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.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!