cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MalGr123
New Member

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:

MalGr123_0-1613378951099.png

  • 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/FilterExample 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: 

 

Measures_InventoryValueLastDate.PNG

Measures_SalesLast2Days.PNG

 

Thank you. If I need to provide more information - I would be happy to do that. 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@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/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
amitchandak
Super User IV
Super User IV

@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/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

hi @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: 

SampleData_BarCharts.PNG

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 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

I think I may not have described exactly what I wanted. But your comment helped to me to get my desired solution. So I am sharing the final measure. 

 

SampleData_MeasureDeadstock.PNG

 

Which gives me the possibility for that bar chart:

SampleData_Visual.PNG

 

Thank you very much 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors