Inventory Management Development Last 2 Years - Stock without Movement

[Anonymous]

Dear Community, 

this my first blog entry in my life 😁. So please be nice to me.

I hope the post is in the correct way and in the correct forum.

Actually i have to understand our stock and i want make some deep analyze. 

My Fact Table "fact_bestandh" is a stock file from sap -> all articles with the stock value & quantity downloaded  at the end of every month from 31.01.2020 - until 30.04.2022

My Dim Table is  dim_calendar and the sales revenue 

Fact Table "fact_bestandh" looks like

Inventory Date	Customer Name	Customer No.	SKU No.	Sales Order No.	Sales Order Position	Stock Value in EUR	Stock Quantity	Paletts	Overdue Date
31.01.2020	Dummy	123456	55-4321	4001	10	1.000	10.000	2	31.05.2020
28.02.2020	Dummy	123456	55-4321	4001	10	800	8.000	2	31.05.2020
30.02.022	no stock anymore

First of all a few further informations.

In the fact table are many articles. One article can have one or n-sales orders. The SKUs have not for every inventory date an entry. 

The big question for us is, why stocks increasing. There for i need your help with building the measures:

1. SUM for each SKU -  I want to select different dates (slicer -> history) -> build sum over article and the beginning stock and actuall stock

2. Increase/Decrease -> -> which skus increase over the last months

3. Stock turnover -> which skus are very fast driver which skus are very slow driver

4. Age of stock -> how old is the stock for each sku. 

5. Overdue in days -> how many days is the sku overdue 

Hope my questions are clear. 

Many Thanks in advance.

Cheers

Max 🙂

[v-jingzhang]

Hi @Anonymous 

The information is not enough so I will ask several questions when sorting the ideas. 

1. Does every article have a distinct SKU?

As the SKUs may not have an entry for every inventory date, you need to add a Dim Article table which has all articles and their SKUs. Build a relationship between Dim Article table and your Fact table on SKU columns. It should be a one-to-many relationship with single cross-filter direction. Dim Article table is at the one side. 

2. For SUM for each SKU, do you want to calculate the sum of Stock Value in EUR or the sum of Stock Quantity? I think the beginning stock should be the earliest stock value in Fact table and the actual stock should be the stock on the selected date, is this right?

3. For Increase/Decrease, how do you want to show the result? Show the stock difference value or show the text result like "Increase/Decrease"?

4. For Stock turnover, how to calculate the stock turnover? What is the mathematical formula? 

5. How to calculate the Age of stock? From when to when?

6. How to calculate the Overdue in days? Is it from the Overdue date and today? Does every SKU have only one Overdue date in the fact table?

I hope you can help answer above questions first to make the expected results more clear. And if you have decided the visual types for each result, please include that in your reply. This will help us understand the expected result greatly. Pictures or screenshots also help. 

Best Regards,
Community Support Team _ Jing

[Anonymous]

Hi v-jingzhang!

Thank yout for your reply! Here are more informations:

1. Does every article have a distinct SKU?

Yes, thats right -> every article has a distinct sku 

-> the table dim_article table has now the relationship to the fact table 

2. For SUM for each SKU, do you want to calculate the sum of Stock Value in EUR or the sum of Stock Quantity?

-> I want to calculate the sum of Stock Value in EUR.

I think the beginning stock should be the earliest stock value in Fact table and the actual stock should be the stock on the selected date, is this right?

-> You are right. 

3. For Increase/Decrease, how do you want to show the result? Show the stock difference value or show the text result like "Increase/Decrease"?

-> here i want to see the stock difference in a table over the months, or in a graph. so that i can see which sku has an movement (increase or decrease) and which sku has no movement.

4. For Stock turnover, how to calculate the stock turnover? What is the mathematical formula? 

-> let us talk about this topic later 😉 

-> i have to build up the dim_sales table for that.

5. How to calculate the Age of stock? From when to when?

Age of Stock = Days of stock is in fact table until the selected date 

so the beginning date until the selected date

-> is it possible to group this 

1. under 100 days

2. 100 days - 1 year

3. more than 1 year

6. How to calculate the Overdue in days? Is it from the Overdue date and today? Does every SKU have only one Overdue date in the fact table?

-> every sku has a overdue date.

-> so the overdue in days is from that date until today 

-> than i can show, how many skus are overdue

Hope my answers are good enough.

Cheers