Incremental refresh on Report with auto refresh Dataflow

[robotbi]

Hi, this may be a silly question..

I need to build a Bi report which shows cost trends from 2019 to yesterday, and table contains 300million rows, so I have to set up incremental refresh.

On Power Bi service: I splited the data into 2 sets: 1) 2019 - 2023 as archived and static;  2) 1/1/2014 - yesterday as dynamic, and I loaded them into 2 dataflows, and set the 2014 dataflow with auto refresh schedule on every morning.

On Power Bi desktop: I loaded the two dataflows and appended the two queries to create this big report, and it takes more than an hour to refresh everyday. Right now, I am trying to set up Incremental Refresh on this report: Ive set it up with parameters and policy and published to Service. 

The goal is to let the report auto refresh every 6am, and have the 2019 - 2023 static data as well as 2014 incremental refreshed data. 

Is there a way to achieve this? Or do I need to hit the report refresh button everyday  after the dataflow finished auto refresh? Thanks much in advance for any advices!

[Anonymous · Accepted solution]

Hi @robotbi ,

Below is my table:

Now you should create two parameters: RangeStart and RangeEnd

Here's a detailed incremental refresh strategy based on your rules:

After that, you can publish it to the service, and then you can set up a scheduled refresh every morning at 6:00 am to refresh.

Static data for 2019-2023 will remain unchanged, while data for 2014 will be progressively refreshed based on date parameters you define.

Best Regards,

Xianda Tang

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