I have been creating a report which needs to show the whole status for the products. For example raw data during the week 1.
During week 2, after I refresh the raw data.
What I need to show in BI report is in this way,
I have did some research (https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/) how to store the historical data.
But its doesn't help, because I retrieve my data from database, there have huge data also with new product coming in. The 'old data' mentioned in the blog have to be refresh weekly and cannot disable the option "Include in report refresh".
Thanks for the attention. Any help provided will greatly appreciated!
There is a way to store old data and refresh on the new data if you use the XMLA endpoint as a data source. This assumes your data has timestamps, and if not you'd need to add some. This is not a best practice, Power BI is not meant to be part of your data pipeline, but this is a hack that will work.
Here is an article explaining in detail: https://medium.com/@shawn.strasser12/save-historical-data-in-the-power-bi-service-with-xmla-endpoint-incremental-refresh-on-any-source-75df6df85572