Get a Count of Orders That are Aged

[boyddt_mn]

Hello, I am making slow progress in my quest to create a report for the Leadership Team. I have the need to report the number of orders that are aged which are orders that are waiting more than 2 days for the next operation to complete. I have a WaitTime_Calculated column which shows the number of days since the operation was completed and I need the minimum value per order to be greater than 2. In the example below order 99999940 would be aged and order 99999959 would not as the last operation completed was 2 days ago. I have sample files that can be found at

https://www.dropbox.com/sh/d6gfg9kns6jdtpa/AAANNd305bBeWR3pHJCh-TEla?dl=0

[v-alq-msft]

Hi, @boyddt_mn 

I'm not very clear about what the expected result will be. Could you please explain the logic and what the calculated column 'WaitTime_Calculated' is like? Thanks.

Best Regards

Allan

[boyddt_mn]

WaitTime_Calculated is the number of day since the last operation for an order was completed. In the spreadsheet order 99999960 last operation [OpAc] is 6/17/2020 from the [postg date] column making the waittime_calculated to be 6 days. Operations [OpAc] that are not 10 have a two day grace period before they become aged orders. Operations [OpAc] that are 10 have a four day grace period. For the same order I have a [WaitTime_Calculated] for each operation that has been completed. So I need to sort out how to just pull the last operation completed since I don't want a COUNTROWS for each operation just one for the order.

Given that I think I solved the issue with the following statement

AgedSummary = SUMMARIZECOLUMNS(SAP_WIP[Order], SAP_WIP[MRP controller], "Days",MINX(SAP_WIP, SAP_WIP[WaitTime_Calculated]),"Aged", DISTINCTCOUNT(SAP_WIP[Aged]))

I then created a measure from this table.

AgedMeasure = COUNTROWS(FILTER(AgedSummary,AgedSummary[Days] >= 4))

This only looks at orders that are four or more days old. It doesn't take into consideration the [OpAc] entry. Which is the next thing I will endevour to fix. We have a meeeting tomorrw to discuss this and the people who know the data better than I can tell me if I'm close.

Let me know if you have any other questions. I'm curious as to how other people would solve this.