Looking for incremental refresh advise

[Siboska]

Hi, 

I would like to get some suggestions (Articles, links, advise, e.g.) on how I can approach my incremental refresh setup in Power BI. 

Case:
I have historical project data which consist of three different type of snapshots. 

Snapshot type1 "end of month": This snapshot show's how a project looked end of every month and will be generated dynamically every month.

Snapshot type2 "Phase changes": Show's how a project looked the day before it moved into the next stage. 

Snapshot type3 "Random date inputs": This snapshot is random date inputs. So e.g. a project manager can write 01-01-2022 and this will generate a snapshot of how the data looked at that specific date in the database. 

So, as you can imaging the tables can get very big which leads to slow query performance and therefore I have just started to look into incremental refresh in Power BI reports. Due to the Snapshot type3 "Random" I  cannot use the RangeStart /End due to the historical aspect were it is possible to order a snapshot back in time. I have thought of  something like, create a custom column where it generates a timestamp for when a snapshot is ordered and use that as a ValidFrom TimeStamp collumn. And if it's possible in Power BI use this to say "Only refresh data 30 days back in time from the ValidFrom timestamp". 

But I'm concerned that this is very performance heavy because the query need to go through all table rows in order to find the ValidFrom TimeStamp. 

Doe's anyone have any suggestion for how I could approach this ?

[edhans · Accepted solution]

I would advise creating a column called "Snapshot Generated Date" that is a DateTime field, then use that field for the RangeStart/RangeEnd parameters.

That way, if someone generates one for Jan 1, 2022 today, it will be 9/20/2022 and will be within the current refresh range, but by Oct 21, it will fall out and move to the first historical partition.

[Siboska]

Thanks for your fast reply. 
And is it also possible Input RangeStart as dynamically looking 30 days back in time?
Also is it possible to configure incremental load across multiple tables at once or do you need to configure this manually for every relevant table?

[edhans]

It is always dynamic. The dates you initially load in are just to get it going. Once you configure the below dialog box, the service ignores the hardcoded dates in the parameters and uses dynamic dates. See Configure incremental refresh and real-time data for Power BI datasets - Power BI | Microsoft Learn ...

You would need to configure for each table. The Select Table box below is where you can set different policies for each table.