BUG with POwer BI Gateway in relation to DAX DATETIME functions and UK dates

[MelissaSnell]

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.

[MelissaSnell · Accepted 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