Not 100% sure if I am posting this in the correct place but I work for an accounting firm and am working on testing powerapps to see if it is useful for clients. My initial project is to build an inventory tracking app in Power Apps that will then allow the data to be used in Power BI for Visualizations. I am stuck at step one which is figuring out a way to structure the data so that I have current quantity on hand as well as being able to filter by a specific date (end of Q4 for example 12/31/2019). My question is how should I structure this, should I use a field in Common Data Service for the date of each physical inventory count or is there a way I can design the app to do a count on "x" date and have it create records for each item and qty as of that date?
You'll find it easier to consume / report on data if you do a count on "x" date and have it create records for each item and qty as of that date. This would be best done on a scheduled basis using Power Automate (aka Microsoft Flow) to run a task on a schedule to create these records. The easiest way to do the count is to use a rollup field; note that rollup fields are automatically recalculated on a schedule (every hour)
Are you tied to CDS for this project?
If you can use SQL (more powerful relational querying) you could create a query that for each product:
* Checks for the most recent stock check date/time and the confirmed quantity
* Sums all of the stock-in and stock-out movements since the most recent confirmed count (up to a specific date/time or to the current date/time)
This has the advantage that the data is alwasy 'live' - i.e. any modification is visible immediately in any reporting, rather than with roll-up fields where the data is only as fresh as the last time the roll-up ran.
Even if your data is in CDS, check to see whether they mirror it to SQL.