Direct Query Refresh not working for Today (Real Problem Identified)

[Kealkil4]

This issue has appeared previously ... and the suggested solution was to amend the SQL Statement ... which is what I had to do to get a worakaround for the problem.

I don't believe that this should be necessary, and I think there is a problem with how the the Native Query is being rendered when you apply a Filter in the Query Editor.

If you apply a Filter of Time.LocalNow() then the date at the time the Query is being generated is what gets passed to the Server.   This works fine on the day that you write it.  But once you publish to the Power BI Service and issue a Refresh on a Mobile the following day, they the query is still looking for the Data as of the day on which you generated the Query.   The date needs to be dynamic ... and the code passed ... [DATE] = GETDATE() or equivalent.  (I'm not a SQL person).

Here  is the Power Query source code ...

let

    Source = Sql.Database("sqlserver", "DemoDB"),

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

    #"Filtered Rows" = Table.SelectRows(dbo_View_Budget_Visitors, each [DATE] = DateTime.Date(DateTime.LocalNow() ) )

in

    #"Filtered Rows"

This is how the Native Query is rendered ...

select [_].[DATE],
[_].[resourceName],
[_].[ADULT],
[_].[CHILD],
[_].[TOTAL]
from [dbo].[View_Bookings] as [_]
where [_].[DATE] = convert(datetime2, '2019-06-11 00:00:00') and [_].[DATE] is not null

[v-qiuyu-msft]

Hi @Kealkil4,

Based on my research, the DateTime.LocalNow() is displaying the UTC time in Power BI service after the dataset is refreshed. You can use the DateTime.AddZone() function to work around the issue. For example, if system timezone is UTC +8, in Query Editor, you can modify the query like below: 

  #"Filtered Rows" = Table.SelectRows(dbo_View_Budget_Visitors, each [DATE] = DateTime.Date(DateTime.AddZone(DateTime.LocalNow(),-8)) )

Best Regards,
Qiuyun Yu