IsInCurrentDay not updating in DirectQuery mode and Entreprise Gateway

[Cyril]

I have a problem with a query, using DirectQuery to build a report through our entreprise gateway. We want to filter out our customer service tickets in order to get only the tickets which were closed during the day :

let

    Source = Sql.Database("XXX", "XXX"),

    Ticket = Source{[Schema="dbo",Item="Ticket"]}[Data],

    TicketToday = Table.SelectRows(Ticket,each(Date.IsInCurrentDay([CloseTime])))

in

    TicketToday

The problem we have is that when we publish the report from PowerBI Desktop to PowerBI.com, it works fine during the day, i.e. the number of closed tickets during the day increases, but the day after, the report still shows the figure of the day before.

We tried another way :

let

    Source = Sql.Database("XXX", "XXX"),

    Ticket = Source{[Schema="dbo",Item="Ticket"]}[Data],

    Today = Date.StartOfDay(DateTime.LocalNow()),

   TicketToday = Table.SelectRows(Ticket,each([CloseTime] >= Today))

in

    TicketToday

This also doesn’t work : after the first day, the report shows the closed tickets of the day cumulated with the number of the day before.

In both cases, the report is based on actual figures (meaning that the enterprise gateway works), but the date of the current day is evaluated during publication at the date of the day of the publication, and is not re-evaluated afterwards. If we republish the report during the day, then it works fine again until the end of the day.

Could you please help me on this issue, which looks like a bug to me…

[pqian · Accepted solution]

It is indeed a problem with DirectQuery. In summary, the M query gets normalized and the date is evaluated on the spot and turned into a constant. Hence it will not update anymore once it leaves the M execution runtime.

Your best bet right now is to turn this particular M query into a native query using the Sql.Database("xxx", "xxx", [Query="xxx"]) syntax. It'll remain in DirectQuery mode and will work after you publish.

On our end we will track this scenario and come up with a better solution (hopefully soon).

Thanks for letting us know!