Evaluation Safety Stock

[Franz]

Hello Everybody

I am asking for help concerning analysing safety stocks in PBI - (please see attached PBIX Sample)

We have three tables:

1. Dim_Calender  contains simple row with dates
2. Dim_Safety_Stock defines a minimum stock level per storage location and item
3. Dim_Stock containes stock data per item, batch number, storage location, handling unit

Connections
Dim_calender & Dim_Stock based on column “date”
Dim_Safety_stock & Dim Stock based on column “Key”  item+location

We would like to calculate two things based on measure

1. How often is available stock (Column “Status” = AVAILABLE) lower than safety stock? (based on location key)

Desired Result

2. What is the difference between available stock and safety stock on daily level (based on location key)

Desired Result

PBIX_SAMPLE.pbix (61.3 KB)

Thanks a lot in advance!

Many greetings from rainy Austria!

[JBocher]

Hi @Franz,

I’ve started to try finding a solution to your problem.

I’ve created several measures that seem to work, but I don’t obtain your results tables above because I think the data you give us is just a sample.

For the first question, I count the rows of a filter table (just the available stock where the stock is under the safety stock)

For the second question, it’s several measures

The problem of my second solution is that it’s working only if you have always data each day for each key even if there is no stock.

For instance in the sample you gave us, there is no stock on 02/08/2021 for 101ABC.

If in your table there is a row with 0 on the stock column, then my measure will tell you that on the 02/08/2021 the difference is -200.

It depends on how your database is made.
Let me know if it doesn’t answer to your question and I’ll keep searching

PBIX_SAMPLE.pbix (76.1 KB)

Best regards,
Joaly

[Franz]

Hi Joaly!

Thank you very much for your help! For a better explanation please see the comments bellow:

We have a data warehouse containing all stock data on a daily basis with handling unit numbers, stock status. In the stock data warehouse we have no line if there is no stock, that is why we have put in the data table.
As a company we would like to know how good we follow our safety stock rules :-).

Regarding Question 1:
We have multiple lines per item and day based on stock status and handling units - therefore we need to sum stock per Key (status = available). Furtheremore we like to know how often (for example in a certain month) we did not fulfill the defined safety stock (including days with no stock).

Regarding Question 2
Yes, you are right sorry for the confusing example. Is it possible to show the - 200 for key 101ABC (Orange) on August 2nd as well?

Thank you very much in advance!

Best regards,
Franz

[JBocher]

Hi @Franz,

I will continue to search for a solution to your problem
With your database, is it possible to use Power Query to transform the data or the change can only be made with DAX ?

Joaly

[Franz]

Hi Joaly,

Thank you very much!

Power Query is no option because this element becomes a part of an exisiting report which is already quite complex (many sources, DWH contains 40 million rows)

Looking forward to your feedback!

Many thanks and best regards,
Franz

[JBocher]

You can find here my solution :
PBIX_SAMPLE.pbix (118.7 KB)

To answer to your problem, I had to create a new table I’ve called “All_Stock”.
This table shows all the different stock per day and per key.

Here is the formula :

Because you don’t have an Item Table that includes all the possible keys in your model, I had to separate my measure in two tables.
You have keys that are in dim_Stock and not in dim_Safety_Stock and keys that are in dim_Safety_Stock and not in dim_Stock.

So the first table (_Table1) calculates the stock, available stock, difference… for the items in dim_Safety_Stock.
The second table (_Table2) calculates all the necessary indicators for the item in dim_Stock that we can’t find in dim_Safety_Stock.
With the function CROSSJOIN, I was able to add all the possible dates and to force the indicators to 0 if there is no value of stock.

Here is a sample of the table All_Stock

I hope this time it will help you.

Best regards,
Joaly

[Franz]

Thank you very much Joaly - you are great - I will try to solve it this way - I think thats the solution

I will keep you up to date

All the best,
Franz

[EnterpriseDNA]

Hello @Franz, just following up if you were able to solve the problem the way @JBocher suggested?

If yes, kindly check the solution that answered your problem.

[EnterpriseDNA]

Hi @Franz, we’ve noticed that no response has been received from you since August 31. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[Franz]

Good Morning Joaly,
please excuse my deleyed answer, we successfully implemented the soultion in our big model (validation not so easy) , so i have exactly what I need.
many thanks again for your effoert quick help.
kind regards
Franz

[BrianJ]

@JBocher ,

Wow! – Absolutely outstanding solution and support on this one. Thanks so much for the support you’ve begun providing on the forum.

• Brian