Data Archiving

[jh123456]

Hi PBI Experts,

I'm currently running a PBI dashboard that grabs data from mulitple sources/reports, some data warehouses and some excel files, and combines these sources to provide a view of daily profitability. The problem is that some of the reports that I'm pulling from get overwritten each day. I need to create a history of the daily data underlying one of my main visualizations so that I can provide a cumulative historical view of performance, for example, year to date and month to date profit/loss. Is it possible to do this automatically within PBI? Could I extract and append the visualization data to an excel/csv archive each time the report is refreshed? To sharepoint maybe?

Many thanks!

[V-lianl-msft · Accepted solution]

Hi @jh123456 ,

You can refer to this blog to calculate YTD, MTD...https://www.mssqltips.com/sqlservertip/4841/calculating-mtd-qtd-ytd-running-and-cumulative-total-in-... 

You can use data flow to save historical data:https://visualbi.com/blogs/microsoft/powerbi/historical-data-preservation-using-power-bi-dataflow/ 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sanimesa]

@jh123456If you have premium capacity, you could probably use incremental refresh.

I found this method to append data without wiping out existing that may be helpful for you:

https://blog.jamesbayley.com/2018/04/23/power-query-how-to-load-only-fresh-rows-and-create-an-excel-...

Another option is to use an intermediate consolidation - for instance you can bring in data daily into an SQL server and build your accumulation there. Then power BI goes to this SQL server instead of directly getting data from multiple sources. This is perhaps the least complicated option that will give you lot of flexibility.