Append changed values to a query

[CarlsBerg999]

Hi,

I have a sales dataset in SAP ByDesign that has different statuses. I connect to this data via an Odata Feed. The status of the sale can change daily from Contacted, quoted and won. I would like to track the amount of days between each status change. For example, how many days passed since the customer was contacted to when the customer was won. 

The issue: The dataset only has "last changed on" -date. Therefore i need to grab the data on a daily basis so i can locate the changes. --> I need to take the original date, append all new changes on a daily basis to the original data and count the difference:

Sales ID	Status	Last changed
1	Contacted	1.6.2020
2	Contacted	12.5.2020
3	Won	31.8.2020
1	Won	1.9.2020

--> The Sales ID 1 would be twice in the data, because i appended the data based on the "last changed" -date. 

How do i set this up in Power BI? Can this be done with incremental refresh?

Thank you in advance! 

[ImkeF]

Yes, incremental refresh would be nice, but it's probably not supported for your datasource, as it must support query folding: Incremental refresh in Power BI - Power BI | Microsoft Docs
Hence my suggestion.

[ImkeF]

Hi @CarlsBerg999 ,

so you have to create a table where you can store historical data and automat the refresh of it on a daily basis.
Setting up a flow in Power Automate would be one option. You could fill a SP list or even write to a SQL DB with it.
Another option would be to setup a Dataverse/CDS dataflow (not Power BI !), as these allow appending new data. (Power BI dataflows will always overwrite).

[CarlsBerg999]

Hi,

Power Automate could indeed provide a solution, but its not the most elegant. Could you clarify the incremental refresh for me, because this seems like a pretty standard thing and I dont understand what is the use of incremental refresh if it doesnt pick up the latest additions to a data set:

- If i have a column that indicates dates, wouldn't incremental refresh just append the existing queried data with the latest dates as determined? For example: If incremental refresh is set to 1 days, shouldnt it check everyday if there are new data entries with yesterday's date and add it to my data?

- I have set up two incremental refreshes in Power BI Service. Both have a 1 day interval incremental refresh set up in Power BI Desktop and then I uploaded them to Power BI Service. For the other one, i also set up a scheduled refresh every day at 8:00 am. Is the scheduled refresh required to set up with incremental refresh?

Im trying to grasp why the existing data is overwritten every time instead of appending it..