Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

@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...

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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...

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 

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.