Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a couple of reports where I need to get the difference between 2 date-times. The data source is a PostGresql database.
This works beautifully on PowerBI desktop (at least it did until I installed the latest version today, but that is a different issue...).
However the data will not refresh in POwerBI - either manually or on a schedule.
I had tried to get the "dwell times" by adding a custom column in the Query, but that didn't work with a different error - related I think to the fact that it was trrying to treat UK dates as US and it thought that the first date was AFTER the second (when it wasn't )
So I thought I would try DAX.
Initially it worked in the POwer BI Desktop, but not in the data refresh via the Gateway. Now it doesn't work in the desktop either
For example
Dwell Time Total Mins = DATEDIFF([Arrived],[Collected],MINUTE)
Now gives an error:
In DATEDIFF Function the start date cannot be greater than the end date
But it's not! I think it is treating the dates as US date format. The Windows 7 OS is on UK region and date-time formats.
The data is a postgresql database on Amazon AWS.
Solved! Go to Solution.
I have "fudged" a workaround for this.
Basically I create a new "DATE" using the year, month and day portions of my date-time field
Dwell Time Total Mins = DATEDIFF(DATE(YEAR([Arrived]),MONTH([Arrived]), DAY([Arrived])),DATE(YEAR([Collected]),MONTH([Collected]), DAY([Collected])),MINUTE)
Initially this worked in PowerBI Desktop, but then the Gateway would error on the refresh.
The latest version of the power BI personal Gateway (Oct 22nd 2015) seems to fix whatever the issue was. Hurrah!
http://blogs.msdn.com/b/powerbi/archive/2015/10/22/new-version-of-personal-gateway-is-now-live.aspx
I have "fudged" a workaround for this.
Basically I create a new "DATE" using the year, month and day portions of my date-time field
Dwell Time Total Mins = DATEDIFF(DATE(YEAR([Arrived]),MONTH([Arrived]), DAY([Arrived])),DATE(YEAR([Collected]),MONTH([Collected]), DAY([Collected])),MINUTE)
Initially this worked in PowerBI Desktop, but then the Gateway would error on the refresh.
The latest version of the power BI personal Gateway (Oct 22nd 2015) seems to fix whatever the issue was. Hurrah!
http://blogs.msdn.com/b/powerbi/archive/2015/10/22/new-version-of-personal-gateway-is-now-live.aspx
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.