Forum Discussion
Incremental Refresh - building historical model week by week
Hello PI folks -
Beginning to read up on incremental refresh and not sure if my scenario applies. I have a data model that needs to be refreshed say weekly but I need to retain the data from the week before. Backstory - 1 or more of the data points in my query updates to realtime data, thus rendering a historical pull useless. So, I need the ability to capture a weeks worth of data at a time and archive it for historical reporting purposes while continuing to pull in and archive data. The end result it eventually have a rolling year or 2 worth of data to analyze.
Is this possible with incremental refresh? If so, what kind of parameter coding goes into it?
Any help would be greatly appreciated!
Hi unknown917,
If the parameters are not set up correctly, this issue will occur.
Ensure that your RangeStart and RangeEnd parameters are correctly named (case-sensitive) and have the Date/Time data type, not just Date.
If the issue persists, please share a screenshot of your parameter setup so we can identify any mistakes.FYI please refer below document,
Configure incremental refresh for Power BI semantic models - Power BI | Microsoft Learn
If this post helps, then please consider Accepting as the solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
15 Replies
Yes, it can be achieved using Incremental Refresh in Power BI.Try to do this
Create RangeStart and RangeEnd parameters in Power Query to filter your Date column dynamically (e.g., last 7 days). Configure Incremental Refresh to retain historical data (e.i in your case, 2 years) while refreshing only the latest week's data, and publish it to the Power BI Service.
Akash_Varuna - Thank you for your quick response! I am not terribly familiar with M Code. Any chance to steer me in the right direction for the code necessary for last 7 days?
I've tried a couple of things fumbling around and haven't cracked it. Here's what I've tried so far:
Last Week Start Date = (Today()-WEEKDAY(TODAY())+1)-7
Last Week End Date = (Today()-WEEKDAY(TODAY())+1)-1
No syntax errors, but once filtered, my query yields no results. Suggestions?
Much appreciated!
Try this
let// Replace with your table
Source = YourTable,
// Define the RangeStart and RangeEnd parameters dynamically
RangeStart = #datetime(), // Adjust the start date
RangeEnd = #datetime(), // Adjust the end date
// Filter rows based on the RangeStart and RangeEnd
FilteredRows = Table.SelectRows(Source, each [Date] >= RangeStart and [Date] < RangeEnd)
in
FilteredRows
Hi unknown917 ,
Yes, this is one exact use for incremental refresh.
You need to set up to M (Power Query) parameters that are DateTime data type, called RangeStart and RangeEnd. You then configure the incremental refresh policy directly against the specific query (table) in either the model or the dataflow and PBI will automatically update the parameter values each time it goes to refresh.
More info:
https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview
Pete
Hi unknown917,
Thank you for reaching out to Microsoft Fabric Community.
Thank you @BA_Pete and Akash_Varuna for addressing the issue.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by Akash/BA_Pete for the issue worked? or let us know if you need any further assistance?
If my response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Anjan Kumar Chippa
Still working through trying to set up incremental refresh. I have set up parameters, but it still says I don't have them set up when I attempt to set up incremental refresh
Are you able to share screenshots of what you're seeing please? Pics of your parameters as they show in the query list, pic of the error you're getting etc.
It's difficult to advise without being able to see/understand exactly what your setup is.
Pete
