Forum Discussion
Incremental refresh with non-rolling archived data
For anybody interested, this is the solution I came up with.
The source is a SQL DB, and the table/view is set to accumulate data from January 1, 2018 onwards. So instead of going back 4 years to capture 2018, 2019, 2020 and 2021 into the archive in PBI, and loosing 2018 next year, I'll set that period to 7 years and will lose my 2018 only in 3 years.
What this will do is that because the source starts from 2018, the report won't have data before 2018 and the fact that it'll keep rolling for 3 years will not make me lose any data in the report dataset.
It doesn't have to be 3 years and can be any time period more than 4 years that I require in my dataset's archive. I'm taking 4+3, assuming that the report will have to be enhanced or modified during these additional 3 years anyway and then I can re-set the rolling period for the archive part again.
Here's the chart for the incremental plan:
If anybody has a better or an alternative solution, please share!
Sorry, but could you elaborate parry2k ?
This is not what I see when I'm testing based on a small range.
Let's say I want to have data starting April 15th (fixed date) and I want my archive to keep growing every day, while making incremental refresh on the last 5 days. What I could do on April 15th is set archive to 10 days and refresh the last 5 days. Both ranges are rolling.
I ran this report refresh on April 25, 26 and 28th and it doesn't store data starting April 15th. It rolls it every day, so as of today, I can only see data starting April 18th but I'd like to have April 15th onwards. Is there a solution for that? Thanks!