Dynamic dates for incremental refresh

[Anonymous]

I'm new to Power Bi so I apologize in advance if I have completely misunderstood this. 

I have a SQL table with a daily snapshot of queue volumes. In total there are years worth of data, but only about 20 rows row are added each day, and historical data are never changed. I feel like this is the perfect scenario for an incremental refresh? I just want to refresh with the new rows from yesterday's snapshot. Here is an example of our table:

DateTime	Queue	Volume
2021-11-03 00:00:00	1	123
2021-11-03 00:00:00	2	456
2021-11-03 00:00:00	3	789
2021-11-02 00:00:00	1	987
2021-11-02 00:00:00	2	654

However, how do I set up the date/time parameters? I want my entire table to be loaded into the model.
I put RangeStart as a static date/time 5 years back in time and I have data from about 4.5 years, so it will be fine.

How do I make RangeStart dynamic so that when I filter my data in PowerQuery the newest data are always loaded into the model? I followed this guide: https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview but they used a static date witch I think would mean not all data are loaded into the model. 

[v-xiaotang · Accepted solution]

Hi @Anonymous 

We create the rangestart parameter by using a static date, but it does not mean that not all data are loaded into the model. 

The 2 parameters RangeStart and RangeEnd, defined in the Manage Parameters dialog in Power Query Editor are initially used to filter the data loaded into the Power BI Desktop model table to include only those rows with a date/time within that period.

After the model is published to the service, RangeStart and RangeEnd are overridden automatically by the service to query data defined by the refresh period specified in the incremental refresh policy settings.

For example, our FactInternetSales data source table averages 10k new rows per day. To limit the number of rows initially loaded into the model in Power BI Desktop, we specify a two-day period between RangeStart and RangeEnd.

so the first question, " how do I set up the date/time parameters? I want my entire table to be loaded into the model. I put RangeStart as a static date/time 5 years back"

A: your current setting is fine, because the parameter is initially used to filter the data loaded into Power BI Desktop

 model table. It will be overridden automatically by the service after the model is published to the service. Then follow these tutorials to set up refresh policy,

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview#define-policy

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.