Forum Discussion
Calculating Deadstock
Hi everyone,
new to the community. I tried to find a solution but didnt find anything - pretty sure it is super easy. I am having an issue calculating deadstock value for inventory that has not moved in a given time period (in this sample data 2 days from the last date in FactOrderlines).
Currently I have the following data model:
- FactOrderlines
- FactInventory
- DimMaterialStore - gives additional information like safety stock
- DimMaterial - gives additional information like material classification
- DimStore - gives additional information like store location
Example of the visual and filters:
Example Visuals/Filter
Issue:
I am not able to create a measure that calculates inventory value of the last date in FactInventory with only materials without any orderlines in FactOrderlines in the last 2 days from the last date in FactOrderlines.
So expected result should be Material C with Inventory Value 200. I can filter that result in the table (Filter Measure SalesLast2Days) but I need a measure to use it in a bar chart. Is that possible?
Currently I have the following measures:
Thank you. If I need to provide more information - I would be happy to do that.
MalGr123 , Create a measure like this
Rolling 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-2,Day))
This last 2 days sales , you can make it N number days as per need
Not sold = if(Isblank([Rolling 2], 1,blank)
Use this as filter for a visual you have common item dimension and inventory value (This measure is not blank is our filter)
The approach is the same as
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458
4 Replies
MalGr123 , Create a measure like this
Rolling 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-2,Day))
This last 2 days sales , you can make it N number days as per need
Not sold = if(Isblank([Rolling 2], 1,blank)
Use this as filter for a visual you have common item dimension and inventory value (This measure is not blank is our filter)
The approach is the same as
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458hi amitchandak . Thanks for the super fast suggestion. Getting a bit closer to my desired solution.
Created the "Not sold" measure just as you described and I am getting to that point that I can create two bar charts like this:
First one with the inventory value of all parts and one with the inventory value of all parts without sales in the given period. Problem is that in my real dataset, I have thousands of different materials, so using that bar chart is not really useful. Is there any way to summarise that information? Basically what I need is one bar with total inventory value and one with total inventory value of all parts not sold without the material dimension as a legend?
MalGr123 , A measure like this should give you count of material sumx(values(Table[Material),if(Isblank([Rolling 2]), 1,blank()))
Or In place of 1 have a measure from the inventory table to give you sum on inventory qty/value
