Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Example of the visual and filters:
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.
Solved! Go to Solution.
@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...
@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:
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.
Which gives me the possibility for that bar chart:
Thank you very much
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |