Hello All,
So I am trying to create a PBI for the need to challenge stock cover of items by warehouses.
It needs several DAX command.
Is there someone that can help me with that?
my model with a picture of Excel result :
https://we.tl/t-lhYi7RRxO6
So my tables of dimensions are :
- "Calendar" (because data like stock, forecast or sales can be "not continuous" with dates)
- "Items"
- "Warehouses"
- "Stock Policy" by item/warehouse and dates (where dates will be continous)
My tables of facts are :
- "Daily Stocks" in tons by items/warehouses/dates : will only include data until yesterday and in the past every day
- "Daily Sales" in tons (same)
- "Daily Forecast" in tons by items/warehouse/dates : will only include data from today and in the future every day (in fact only a figure each Monday, other days empty, not a problem)
In my attached data model, consider that today = 15 July 2024
My report should have as filtering segment the item code (coming from the dimension table items)
I also would like to have the possibility of having a box where the user can enter a manual figure (don't know if possible) and this figure will be taken for some DAX. If not possible, I think we can include another segment/filter coming from a new table that will have for example all round numbers from 0 to 100, and user will choose one instead of entering manually.
I would need a Matrix or table (one by warehouse / in my example 3 warehouses exist so 3 tables) with calendar days as columns (continuous data)
And for the lines : (will depend of item selected by user)
I don't even know if that kind of matrix exist or if I am too close to Excel format.
Line 1 : I want to display the selected item stock policy in days for each date that are in columns (table Stock Policy) so here, no DAX I guess
Line 2 : I want to display :
If inferior strict of Today : Sum of sales by date
If equal or superior of Today : Sum of forecast by date
Line 3: I want to display "Current Stock Level" = sum of stocks by date (today and future will be empty ok)
Line 4 : I want to calculate "Current Target Stock" : For example on column 12/07/2024, item 123456, warehouse USA1, stock policy is 3 days so I want a DAX that do : sum of Line 2 for the next 3 days
Line 5 : I want to calculate "New Target Stock" : Same logic as Line 4 but taking into account the manual figure the user can put somewhere (or selected from a rolling list if not possible). Let's say he choose to select "2", Line 5 should do sum of line 2 for the next 2 days.
Line 6 : I want to calculate "New Stock Level" = "Current Stock Level" minus ["Current Target Stock" minus "New Target Stock"] all from same day same column
Line 7 : "Check Stock Coverage" : if Line 6 is error, nothing otherwise equals = "New Stock Level" minus Sum of Line 2 for the next 4 days (4 days is fixed figure in DAX, no need for user to change it)
And finally I would love to have somewhere on the report a box saying how many days of "Check Stock Coverage" that have negative figures.
