Relative Date filter (IsInPreviousNMonths) is not refreshed

[curufinwe]

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 maintanable solution if I want the report to automatically display correct data.

Is it a bug or expected behaviour?

Thanks,

Sergey Yerdekov

[v-haibl-msft]

@curufinwe

In Power BI Desktop, you need to click Refresh to manually refresh the data. It cannot be refreshed automatically in PBI Desktop.

If you publish it to PBI Service. After you configured on-premises gateway for this SQL data source, the report can be refreshed automatically in Power BI Service.

Best Regards,

Herbert

[curufinwe]

@v-haibl-msft

The actual issue was with Power BI report (using DirectQuery connection) deployed either to Power BI service or Embedded Power BI. 

It can be reproduced in both cases. Azure SQL server was used, so no gateways are needed.

I've got an answer from Microsoft employee confirming that it is a known issue: link

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.

Best Regards,

Sergey Yerdekov