Hi,
I have a snapshot kind of source table. It contains current stock inventory levels like this:
Product / Number on stock
A / 345
B/123
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.
Expect this is doable in Power Query. But it will depend on the available data so a few questions.
These snapshots of Inventory Stock levels, how are they generated?
Does this contain a stock value for each product each day?
Only values for changed Product stock levels each day?
Or …?
Will this data remain available?
Like in a sharepoint folder or something like that?
You would also need to ‘know’ the export date for each of the snapshots…
Hi Melissa,
the situation is that no historic data is available at the source, only current inventory levels for all product as of now (time of query). So the solution must definitely store daily data and append changes by looking up the last inventory level of each product and then store changes. If the solution can only store current values and not look up past values and calculate changes this could be done in later steps in Power Query or DAX. If there are no changes for a product then no value for the product must be stored for that day. But removing 0-changes in a final step would not be an issue.
There is a “last changed when” information that could be used, so instead of not storing product/date-lines with 0-change the solution could also exclude lines where the last changed when date for the product is older than the previous query date. In this case you would have to compare dates instead of inventory level numbers if this is easier to do.
It is definitely not possible to restore the whole history by running a query once. If the history is lost then it’s lost. So the solution must be able to provide some store and append mechanism like an incremental load or adding rows in an SQL table.
Another typical use case of this would be web-scraping from a web-page that shows the current value of something and you want to build a history of it.
Best regards,
Martin
The key is to store these historic snapshots that are generated. But I’m no Data Architect so I’ll limit my response to what Power BI has to offer.
.
You can explore incremental refresh, if you haven’t already, here is a link to the GA announcement.
.
Or you can explore setting up a Push Dataset: This is a special case of the streaming dataset in which you enable Historic data analysis in the Streaming data source configuration dialog.
Push datasets are stored in Power BI online and can accept data via the Power BI REST API or Azure Streaming Analytics. One of the most interesting things about Push datasets is that, in spite of providing 5 million rows of history by default , they do not require a database . We can, in fact, push streaming directly from a source such as a device or executing code to Power BI Online’s REST API.
.
There are other possibilities of course like storing all snapshot files in a SharePoint / OneDrive folder or something like that…
You can pull the information directly from website by using power query (Get Data - web) within Power BI.
i would want each day ending inventory count be applied to a table that can be used for the calculation.
If the source data doesn’t have the date, you will need to update that information while in query mode before you append the information to main table for ending inventory count on a daily basis.
I’m sure something would be on youtube or within Enterprise DNA that will be calculate the daily activity using dax calculation. You likely could also make a table for the daily activity.
That would be my process going through the what you stated.
Hi @Martin, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!