I am using the following query (when connecting to SQL database in Direct Query 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 generating SQL query with exactly the same dates. I expected the dates be '2015-12-01' and '2016-12-01' to display 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 behavior?
This behaviour appeared to be a known issue.
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.