Showing the last N days of data with incremental refresh

[Timsonater]

Hi there, 

I have a powerBI report in which I would like to show the last 3 days (Meaning, the current date and 3 days previous) of data in visuals when published. I have setup incremental refresh and defined RangeStart and RangeEnd values, which as I understand are updated when the report is published.

The published report seems to only display data within the RangeStart and RangeEnd timeframes. Is there a way to set these values so data is incrementally refreshed to be in the last N days? Something like:

RangeStart = CurrentDate-3 Days

RangeEnd=Today

The report itself has a significant amout of data and it is only reasonable to keep the last 3 days of data.

Am I misunderstanding something in this setup? Is incremental refresh only for refreshing data in a set timeframe, or can it be used in this way? Is there another method I should be using to accomplish this?

[v-rongtiep-msft]

Hi @Timsonater ,

"The published report seems to only display data within the RangeStart and RangeEnd timeframes." 

----No. The Archive data starting before refresh date setting determines the historical period in which rows with a date/time in that period are included in the dataset, plus rows for the current incomplete historical period, plus rows in the refresh period up to the current date and time.

For example, if you specify five years, the table stores the last five whole years of historical data in year partitions. The table will also include rows for the current year in quarter, month, or day partitions, up to and including the refresh period.

More details:

Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Learn

[Timsonater]

Thank you for your response, 

Maybe I did not phrase my question correctly. I only want to display data in the last 3 days in my report. Is this possible with incremental refresh? Is there another tool I should be using to do this?

I don't understand how the advancement of the data window in that image is occuring, as the ranges I am entering for RangeStart and RangeEnd do not seem to change.

[v-rongtiep-msft]

Hi @Timsonater ,

This image shows that you imported the data from 2023/3/23-2023/3/25, but the refresh is 2023/3/26, so it is not reflected in your refresh. You can try ba changing the 1 day below to 3days.

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.

More details:

Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Learn

Best Regards
Community Support Team _ Rongtie

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

[Timsonater]

Thank you for your response.

So if I change the settings like so:

And I publish the report and wait a month, say until 4/30. Will the report still contain data for the last 3 days? (4/27->4/30)?

[v-rongtiep-msft]

Hi @Timsonater ,

Yes. With each subsequent refresh, the query filters return only those rows within the refresh period dynamically defined by the parameters. Those rows with a date/time within the refresh period are refreshed. Rows with a date/time no longer within the refresh period then become part of the historical period, which isn't refreshed. If a real-time DirectQuery partition is included in the incremental refresh policy, its filter is also updated so that it picks up any changes that occur after the refresh period. Both the refresh and historical periods are rolled forward. As new incremental refresh partitions are created, refresh partitions no longer in the refresh period become historical partitions. Over time, historical partitions become less granular as they're merged together. When a historical partition is no longer in the historical period defined by the policy, it's removed from the dataset entirely. This behavior is known as a rolling window pattern.

There are more information about incremental refresh:     Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Learn

Best Regards
Community Support Team _ Rongtie

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