Hi all, got a problem really bother me, I can’t figure out a way to solve this situation, pleaseeee help!!!
I calculated a total number of my inventory, and display this number every day, but it changes everyday too, for example, the total number for today is 300 based on counting inventory status, then tomorrow the total number changes to 295, then the next day, this total number changes to 305.
This inventory status have the inventory date, which could be future date as well, but my total count only based on the status name.
is there a way to get this total number record and to show this 300, 295,305 side by side on the timeline, so i can see the trend of the supply of the inventory?
I uploaded a sample pbix file. as you can see from the file. i can easily calculate the total ITD “Spec inventory” using the status name. It shows 112 since inception to date. tomorrow it might changed to 109 if some spec inventories are sold. What I’d like to see is how can I record these total inventory and show them on the time serials to see the changes. I drew out the chart to mock up what I want to see.
Unfortunately the file is not downloadable.
If I understand correctly your problem you actually need a “snapshot” every day, right?
If this is the case and my understanding is right I see only two big possibilities:
at the data source Level build a table where you insert a daily detailed “snapshot” so you will be able to see trends and do the analysis on that specific table
depending on the granularity of your data in the fact table do the analysis in DAX. I’m saying that it depends on the granularity because you might have in the data model ALL the transaction in-out from inventory or you might have a pre-aggregated table with just a number for a specific date … and this is why I said a sample data would help.
I would choose the first option as this would give you additional options to do analysis like details on the parts in stock, aging on different parts, inventory trends by families, etc.
Thanks for posting your question @Howie. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.
Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Including all of the above will likely enable a quick solution to your question.
Thank you Cristian, you are right, I am trying to snapshot every day counts. I think the first option is good idea. I was trying to create a column on my “Date table” to record the total count based on if the date = today’s date, but it’s not quite there yet. Do you know how I can insert this everyday’s snapshot in a table?
When I was talking about “snapshot” I was referring to an actual set of detailed data saved in a database at a certain point in time with a SQL Job like method.
I am using this method for different reports like “Daily bone-pile” (defective units in production) and to count the units it’s just a COUNTROWS () function used.
Of course, if you need a trend and the measures are semi-additive you can use LASTDATE () or LASTNONBLANK() functions.
In some cases I have two Fact tables: one for a week or two with detailed data if the end user really needs to go to the Item level and an aggregated table on which I can do trends and higher level slicing and dicing.
I have uploaded a table with aggregated stock metrics for an inventory of 3 products. This is in support of your preferred approach, at this point in time. Take a look at the formulae for the fields “StartingStockCount” and “EndOfDayStockCount” to better understand the concept and see if it is what you want.
How to automate the process will depend on the technologies that you are using for your data management and the design of the data and BI system.It is highly recommended to have is as a Job, as @Howie mentioned, so as to automate the process. Another approach would be to have the “snapshot” table as a view.
If you are looking for option in Power BI, Do check if you can add some fields in your data like Date Item Sold,Added etc, so that Inventory Count for each day can be calculated inside Power BI.
If you can share sample PBIX file, then may be I can provide some alternatives.
Hi @Howie, 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!
(69.8 KB)