I have a snapshot kind of source table. It contains current stock inventory levels like this:
Product / Number on stock
A / 345
C / 750
I need to show a diagram that shows stock level changes on a time axis with a granularity of daily. Therefore I need to take daily snapshots of the stock levels and transform them into a fact table that looks like this:
Date / Product / Inventory change
12/05/2020 / A / -20
12/05/2020 / B / +10
13/05/2020 / B / -50
13/05/2020 / C / -8
Can you think of a blueprint solution for this that works with Power BI, Power Query and automated refresh in Power BI Service only, or do I need external tools to prepare, store, and refresh this data, like SQL-Server and SSIS? What’s is the best practice to deal with this common scenario in Power BI, ideally as a pure cloud SaaS solution, most ideally as a pure Power BI solution? It is usefull for any kind of data where you can only get current totals but not the individual transactions that generate these totals.
Thank you very much!