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?

[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.