Dynamic Inventory Age Tracker

[ageraci]

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.

ID	InDate	OutDate	Depot	RepairDate	EstDate	Waiting Estimate Test Days	Waiting Repair Days
1	09/04/18	09/11/18	Loc 01	09/05/18	09/05/18	FALSE	FALSE
2	09/04/18	09/19/18	Loc 01	09/19/18	09/13/18	FALSE	FALSE
3	09/04/18	10/09/18	Loc 01	10/04/18	09/13/18	FALSE	18
4	09/04/18	09/25/18	Loc 01	09/22/18	09/22/18	FALSE	FALSE
5	09/04/18	09/26/18	Loc 01	09/22/18	09/22/18	FALSE	FALSE

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. 

[v-lili6-msft]

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])

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

Result:

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

[Greg_Deckler]

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

[ageraci]

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.

[ageraci]

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.