Forum Discussion
Time/Date Problem when publishing to Power BI Service
Where abouts do you input that formula, Anonymous ?
hi michael_knight - I have used that formula in a while I was creating a DAX measures for one my report KPIs.
Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!
Can you post a PBIX file with it included? I'm interested to see how it works Anonymous
I tried creating another column which +1 days and that worked until it hit December when it stopped -1 to all of my dates. Really puzzling
hi michael_knight - thee report has some customer data - I shall mock up something and share it with you shortly. Thanks!
Are you creating your date columns using DAX or are you pulling those values directly from a datasource?
Power BI Desktop will always refer to your local datetime when you use formulas like NOW() because it has reference to your local time. However, when you publish to the service - these formulas shift to UTC time - and must be corrected by adding or subtracting time (as Anonymous showed).
You can easily test this by publishing a Power BI desktop file to the cloud with variations of the Now() and UTCNow() formulas.
Here are the formulas in the Power BI Desktop file - but as a measure and as calculated columns on a table.
When its published to the service - the DAX measure changes to UTC immediately - but the calculated columns remain the same as they appeared in PBI Desktop (since they are imported values).
However - as soon as the model is refreshed in the service - everything now adjusts to UTC (including the calculated columns)
Hi swise001 and Anonymous ,
I import the data from Dynamics 365 and include the date table that I use for all my reports. I change the Data types with the dates in from Date/Time/Timezone to Date. The reason for this is because the Data Types for the date in the Data table and the Date table need to be the same if I want to create a relationship between the two
After I've done that, I create the formula, and Include the USERELATIONSHIP function in order for the Dates in the Data table to use the Dates in the Date Table in a visual
I just made a sample dataset of what I'm working with, with a link to what it looks like published. I also duplicated the Date columns I'm working with so you can see what it looks like before and after I make the changes. As you can see the figures are different in both the PBIX file and the Published version. The figured in the PBIX file are what I want.
https://www.dropbox.com/s/7h70duzrrnb2ksf/comp%20test.pbix?dl=0
Cheers,
Mike
