Incremental refresh with SQL date filter

[Anonymous]

I have a few SQL queries that I want to set up incremental refresh on because of their size.

My question is then: if the SQL queries/statements contain date parameters, how will they work together with the incremental refresh?

Eg. if I in the SQL statement takes in data from 1.3.2017 (static) until today's date (dynamic), but I limit my incremental refresh to only the last two years and the refresh to only the last 14 days. What will happen when we cross 1.3.2019?

I'm not sure this is a problem, but I'm having a hard time figuring out if the incremental refresh or the SQL statement "wins"?

[v-juanli-msft]

Hi @Anonymous

How do you cross 1.3.2019?

Does it use in a filter or parameters(RangeStart and RangeEnd)?

Additionally, you could refer to these article.

Best Regards

Maggie

[Anonymous]

Thanks, but that article doesn't seem to answer my question.

I guess my question needs to be clarified more.

In the SQL statement there is a static date filter set for 1.3.2018, so I will get the data from 1.3.2018 to today's date.

When setting up incremental refresh in Power BI, like in the article you reference, I can set the refresh to only store data 12 months back in time (The "Store rows in the last:" parameter).

The question then is, what happens when we arrive at 2.3.2019?

The SQL statement will try to get data from 1.3.2018 to 2.3.2019, but the incremental refresh will try to limit the data to 2.3.2018 to 2.3.2019.

As I see it three things can happen:

1. The incremental refresh just filters out the data outside the range

2. The data from 1.3.2018 will persist in the dataset, thus breaking the rule set by the incremental refresh

3. The refresh breaks entirely

I could just wait and see, but it would be nice to know beforehand if anyone has experience

[Yakapo]

No answer as of yet it looks like. I would suggest experimenting with a test file with a shorter date range, and see what happens when you use the incremental refresh. My initial thought is that if you use the optional SQL query, the data specified in that query is brought into PowerBI, and then you apply filters with incremental refresh. To me, that means the query won't be any quicker, so incremental refresh would be pointless.

It would be nice to be able to do and incremental refresh in the optional SQL query, but I am not sure that is possible. You have me curious, so I will play around with a current dataset I have and see if I can get it to work.

[Anonymous]

Hi,

Has there been any solution to this?

[GilbertQ]

If it is setup correctly it will work as expected.

Possibly watch this video by Guy in a Cube, who does a great job showing you how it works: https://www.youtube.com/watch?v=CajQjq70Kpg