Need dataflow to incrementally refresh from fixed start date rather than rolling time period.

[Mark_Ritter]

I have a dataflow setup to read bookings from our Oracle database using incremental refresh.  I need the data to start at 1/1/2022 and have each day's bookings added incrementally through YTD.  I set the "Refresh rows from the past" parameter to 1 day, which works as expected.  If I set "Store rows from the past" to 1 year, the data goes back to 1/1/2021, not 1/1/2022.  The routine used to pull the data is not valid before 1/1/2022 so I don't want that.  If I set that parameter to n days, n being today's day number in the year, I get all the YTD days correctly, but then each day after that the new data is added and the oldest day drops off, i.e. it gives me rolling n number of days.  I just set it to 1 quarter and am waiting for refesh to complete.  I am pretty sure it will give me data from 1/1/2022 to present. But when Q2 is over, I expect Q1 will drop off.  I suppose I can set it then to 2 quarters back, but suspect that will require a full refresh, which takes a long time.

Can I get the incremental refresh start date to be a fixed date instead of rolling time period?

[asocorrocognex]

There is a way to do this.  Let's say you want data from 1/1/2019 to always be present.  So for yearly archived partitions, you want the process to always add years but never remove 2019, 2020, etc. 

The idea is to ask for a large number of years to be archived, such that 2019 is far from the initial year.  Let's say you ask for 20 years.  That would make the first partition be 2006 (it's now 2025).  This ensures that the 2019 partition won't be wiped out until about 2038 (if your report is still around, then you're a genius).  

Since you don't want any data from before 2019, you then modify the filtering step in your query to prevent loading data into the partitions between 2006 and 2018.  For example, if your filtering step is currently like this:

[CREATE_DATE] > RangeStart
and [CREATE_DATE] <= RangeEnd

change it to

[CREATE_DATE] > RangeStart
and [CREATE_DATE] <= RangeEnd
and [CREATE_DATE] >= StartDate

where StartDate is 1/1/2019.  The partitions will be created but they will be empty.

[JanB42]

Does anyone know if this is still not possible without XML endpoint? I've been looking for quite some time but could not find anything.

YTD values are used in so many business scenarios, this would be a really nice feature, e.g. just setting "0 Years" and it starts at the beginning of the year

[Mark_Ritter]

The tools described in the first link look like they would allow me to do what I need and then some.  Lots of new stuff to learn. I am in exploratory mode now, but if the company I work for is going to use PBI services with  incremental refresh for business critical BA/BI reporting, we will need this higher level of control.  Thx, Mark

[v-yalanwu-msft · Accepted solution]

Hi, @Mark_Ritter ;

May be you could try to Advanced incremental refresh and real-time data with the XMLA endpoint.

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-xmla

https://docs.microsoft.com/en-us/power-query/dataflows/incremental-refresh

https://www.youtube.com/watch?v=JsJWBr1_ktQ

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.