cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ageraci Frequent Visitor
Frequent Visitor

Dynamic Inventory Age Tracker

I have been banging my head against this problem for the last week and could really use some assistance.

 

What I am trying to do:

Calculate the age of inventory that is waiting to be repaired or have estimates posted. and put that in a line chart to track the trend of total days that inventory is sitting waiting for some work to be done. I want to see a calculation that dynamically tracks all the previous entries to get a total amount of waiting work and the length of time that it has been waiting.

 

What I am working with:

The below table is what I basically have (the "Repairs and Estimates" table), the waiting Estimate Test Days and Waiting repair days are both excel equations that I set up to calculate the amount of days that ought to show up when the line chart is showing a range of days but is specifically showing 10/1/2018. In ID 3's case I should see 18 days showing as the estimate was done 9/13 and the actual repair was completed 10/4.

IDInDateOutDateDepotRepairDateEstDateWaiting Estimate Test DaysWaiting Repair Days
109/04/1809/11/18Loc 0109/05/1809/05/18FALSEFALSE
209/04/1809/19/18Loc 0109/19/1809/13/18FALSEFALSE
309/04/1810/09/18Loc 0110/04/1809/13/18FALSE18
409/04/1809/25/18Loc 0109/22/1809/22/18FALSEFALSE
509/04/1809/26/18Loc 0109/22/1809/22/18FALSEFALSE

 

I would want to view this as a chart and then also get into averages of the data to see for example a rolling period of say 6 months for a location - to identify if a particular location is losing control of their repair/estimate schedule.

 

Thank you to anyone who can aid me. I have been failing at this for what feels like forever. (Or even direct me to an already made solution)

 

*Edit 1:

A few thoughts that seem to maybe make it more possible:

I currently had a relationship set up between a date table and the In date column of the table which is tying everything to the indate. I want the math to basically apply to the date to dynamically calculate, between all the rows of the repairs and estimates what the values of the repair days are compared to the actual calendar. 

4 REPLIES 4
Super User
Super User

Re: Dynamic Inventory Age Tracker

Having some difficulty understanding exactly what you are going for here but maybe take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


ageraci Frequent Visitor
Frequent Visitor

Re: Dynamic Inventory Age Tracker

Thank you for the input, I am going to see what happens with those as a possible solution. I also edited my question with a little bit more information.

Community Support Team
Community Support Team

Re: Dynamic Inventory Age Tracker

hi, @ageraci

After my test, If you could use this way as below:

Step1:

Add a calendar table

Step2:

Use crossjoin Function to add a new table like below

Table = FILTER(CROSSJOIN(Table1,'calendar'),'calendar'[Date]>=Table1[EstDate]&&'calendar'[Date]<=Table1[RepairDate])

7.JPG

Step3:

Then add these two measure 

Measure = IF( DATEDIFF(MAX('Table'[EstDate]),MAX('Table'[Date]),DAY)=0,FALSE(),DATEDIFF(MAX('Table'[EstDate]),MAX('Table'[Date]),DAY))


Measure 2 = CALCULATE(SUMX('Table',[Measure]))

Step4:

Create the relation between new table with calendar table

8.JPG

Result:

9.JPG

 

here is my pbix, please try it.

https://www.dropbox.com/s/bvtbtc9nbh94r75/Dynamic%20Inventory%20Age%20Tracker.pbix?dl=0

 

hope this will help you.

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ageraci Frequent Visitor
Frequent Visitor

Re: Dynamic Inventory Age Tracker

Greg,

 

Is my understanding correct that your solutions are generating tables in memory as opposed to the possible other solution posted here:

https://community.powerbi.com/t5/Desktop/Dynamic-Inventory-Age-Tracker/m-p/560668/highlight/true#M26...

 

Which is actually creating a table and running from that newly generated table.

 

Do you have any idea of the efficiency of that method with large datasets vs. your method? I'm just trying to figure out which method I should actually go for and if it's worth it for me to do the work to figure out how to convert your suggested solutions to my needs.