cancel
Showing results for
Did you mean:
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.

 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.

4 REPLIES 4
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

Proud to be a Datanaut!

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

## Re: Dynamic Inventory Age Tracker

hi, @ageraci

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

Step1:

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:

```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

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