Forum Discussion
Calculating dates between comparing dates from 3 tables
I have 3 different tables where I'm trying to calculate cycle time. All 3 tables have the serial number associated with the part as the relationship.
First table is inbound ...unit serial number scanned in upon receipt.
Second table is outbound.... unit serial number scanned upon shipment.
Third table is scrap.... unit serial number scanned upon scrap (not a viable unit)
Is there a way to calculate the # of working days (monday - friday) it takes for processing from the 1st table to either the 2nd or 3rd table. Remember the unit will always be on the first table, but depending on whether it is a viable unit, it could be in either the 2nd or 3rd table.
Data:
Inbound unit received 5/23/18 Serial Number 123456789
Unit processed and shipped 5/30/18 Serial Number 123456789
But I still need a query from the 1st table to either the 2nd or 3rd table, and this is where I'm uncertain.
Thank you
4 Replies
Debbie_Montique,
Please help to share sample data of the three tables and post expected result based on sample data here.
Regards,
LydiaHello Lydia,
Below is what I have so far, notice that the inbound serial number (left table) was received on 5/17/2018, and we see that it was shipped out on 5/29/2018 (middle table). Easy enough to see that the cycle time would be 7 working days (Sat-Sun and holiday excluded). However, if it were a scrapped unit, it would appear in the far right table (currently you see empty in the screenshot). What I'm having a problem with is how to calculate the # of working days from the inbound to either the shipped table (center table) or the scrapped table (far right table).
Thank you so much for your help!
Debbie_Montique,
Do you create relationships among these tables? You can create calculated columns in the first table to bring date columns of second table and third table into the first table, then calculate working days following the guide in the similar threads below.
https://community.powerbi.com/t5/Desktop/Number-of-days-between-two-dates-but-only-counting-work-business/td-p/158820
http://community.powerbi.com/t5/Desktop/Number-of-working-days/m-p/256119
Regards,
Lydia
