none
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.

     

    Thanks,

    Sergey Yerdekov

    Friday, December 9, 2016 3:39 PM

Answers

  • 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.

    Ehren

    Tuesday, December 13, 2016 12:42 AM
    Owner