Forum Discussion
Time/Date Problem when publishing to Power BI Service
Hi,
I've got a problem with my Date/Time when publishing to PBI Service
This dataset I've got is used to show the completions in the business. I have a Completion Date and I converted it from Date/Time to a Date column. The reason I convert it is because it makes it possible to create a relationship between the Completion Date and the Date from the Date table
The image on the left is the date when converted, and the image on the right is what it looks like not converted. The 2 values at the bottom are what I'm struggling with. All of the date on the left are correct and I'm happy with, the dates on the right are where I think the issue is. As you can see, it's showing the bottom two as being in August rather than September
I think this is happening in the Power BI Service, as them two records aren't showing up in September like the column says, but it shows them in August
Has anyone experienced this? I need any help
Cheers,
Mike
21 Replies
A few questions:
Do these same inconsistencies show up in the Power BI Desktop file (prior to publishing to the service) - or only after connecting to the dataset after it has been published?
What means (formula, etc) did you use to strip away the time and keep the date?
If you have access to the Power BI Desktop file where the conversion was done - this is where I would start - specifically at the step where the time was removed and date was kept.Hi swise001
The screenshot I posted is from Power BI Desktop, and it works fine in that and I'm very happy with the numbers. Once I upload it to the Power BI service, it -1 day for every single record
I don't use a formula, I change the datatype with the drop down option in Desktop
I use the USERELATIONSHIP function in order to connect the completion date to the date table
hi michael_knight, can you confirm if you are using PRO license or a Premium license?
Also are you using any TODAY() or NOW() in DAX?
I have experienced that these above functions will return the Power BI service server time when used - and this causes issues in reports. I had to implement some logic to calculate back to US timezone to ensure my report logic works as expected.
Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!
Right, I've cracked it with a help from you two!
You both helped in identifying the issue in terms of the times changing when publishing, so I did research on how to change the time
I used this article: https://natechamberlain.com/2019/08/02/add-or-subtract-hours-from-date-time-values-in-power-bi-using-m-formula-in-power-query-editor/
=[Completion Date] + #duration(0, 1, 0, 0)
I used that formula in the Custom Column section in the Query Editor. I felt that the difference in time wasn't significant so I just change the time so instead of 11pm, it showed 10pm and that gave me the desired result.
The hard thing to understand was that the Desktop version is always going to be wrong because time time/dates change but when I publish it to the service is when the figures become identical to Dynamics 365
hi michael_knight - Glad you got this worked out; I also enquired and there are some reports we have built for one of our customers which is using data from Dynamics 365 but we are not seeing this issue.
I think you should still open a ticket with Microsoft to look into this. 🙂
Also curious to know - did you try the calculated column approach - did that work as expected?
Well this is the really the only time I've seen this issue. Most of the date data we use in Dynamics 365 has Time and Date, but for this instance it's only a date so maybe that's where the issue lies. I'll contact Microsoft anyway and see if it's an issue
I didn't get to trying the calculated column appoach, that was going to be the 2nd thing I tried today but thank god the first thing worked!
Thank for very much for your help, it was much appreicated!
