Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
boyddt_mn
Helper III
Helper III

Get a Count of Orders That are Aged

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

MinOrder.PNG

 

 

2 REPLIES 2
v-alq-msft
Community Support
Community Support

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

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors