Relative Date filter (IsInPreviousNMonths) is not refreshed RRS feed

  • Question

  • Hi all,


    I am using the following query (when connecting to SQL database in DirectQuery mode) to get appointments for the previous 12 months:


    Table.SelectRows(dbo_ReportAppointmentsView, each Date.IsInPreviousNMonths([Date], 12))


    It was working perfectly fine and was generating SQL query with where clause like:

    "where convert(date, [_].[Date]) >= convert(datetime2, '2015-11-01 00:00:00') and convert(date, [_].[Date]) < convert(datetime2, '2016-11-01 00:00:00'))"


    The problem is that when current month changed (November -> December) it is still generation SQL query with exactly the same dates.

    I was expecting that the dates should be '2015-12-01'  and '2016-12-01'  to display a data for previous 12 months.

    It helps to press "Refresh" button in Power BI desktop but it is clearly not a maintainable solution if I want the report to automatically display correct data.


    Is it a bug or expected behaviour?

    P.S. Actually if I use DateTime.LocalNow() function in such query filter - it also becomes cached and is not updated.



    Sergey Yerdekov

    Friday, December 9, 2016 3:39 PM


  • Hi Sergiy. The behavior you're observing when using Direct Query against SQL is a known issue with the legacy Direct Query architecture. We will be moving SQL Direct Query to a new architecture over the next few months, but until then there's unfortunately no workaround besides manually refreshing.


    Tuesday, December 13, 2016 12:42 AM