Calculate Average Per Month for Open Orders

[Seif949]

 I am having difficulty producing a line chart that shows the average number of days between order date and last ship date for orders that have yet to be completely shipped.  An order may have multiple records with different shipping dates but I'm only interested in counting orders that have not completely shipped by the end of the each month. 

The data is contained in a 'DateTable' and 'OrderTable'. 'OrderTable' contains [Order Number], [Part Number], [Order Date], [Ship Date, and [Days to Ship]. There is an active relationship between 'DateTable'[Date]:'OrderTable'[Order Date] and an inactive relationship between 'DateTable'[Date]:'OrderTable'[Ship Date].

Thank you so much for any and all help!

Here is an example of the OrderTable and a corresponding Excel line chart.

Order Number	Part Number	Order Date	Ship Date	Days to ship
1	14	1/3/2024
1	29	1/3/2024	3/23/2024	80
1	24	1/3/2024	3/23/2024	80
2	25	1/13/2024	5/11/2024	119
2	44	1/13/2024	3/20/2024	67
2	15	1/13/2024	4/29/2024	107
2	42	1/13/2024	4/10/2024	88
2	15	1/13/2024	5/3/2024	111
3	25	2/4/2024	5/11/2024	97
4	38	2/6/2024	3/6/2024	29
4	18	2/6/2024	2/28/2024	22
4	20	2/6/2024	5/24/2024	108
4	30	2/6/2024	5/29/2024	113
5	12	2/21/2024	5/4/2024	73
5	46	2/21/2024	4/5/2024	44
5	27	2/21/2024	3/7/2024	15
5	26	2/21/2024
5	39	2/21/2024	4/19/2024	58
5	24	2/21/2024	3/4/2024	12

[Anonymous]

Hi @Seif949 

The screenshot you provided is less detailed.

Can you explain the logic of your calculation and how you arrived at the expected result?

Regards,

Nono Chen

[Seif949]

I appreciate your patience and thank you for your time!

Order Number	Part Number	Order Date		Ship Date		Days to ship
1	14	1/3/2024
1	29	1/3/2024		3/23/2024		80
1	24	1/3/2024		3/23/2024		80
2	25	1/13/2024		5/11/2024		119
2	44	1/13/2024		3/20/2024		67
2	15	1/13/2024		4/29/2024		107
2	42	1/13/2024		4/10/2024		88
2	15	1/13/2024		5/3/2024		111
3	25	2/4/2024		5/11/2024		97
4	38	2/6/2024		3/6/2024		29
4	18	2/6/2024		2/28/2024		22
4	20	2/6/2024		5/24/2024		108
4	30	2/6/2024		5/29/2024		113
5	12	2/21/2024		5/4/2024		73
5	46	2/21/2024		4/5/2024		44
5	27	2/21/2024		3/7/2024		15
5	26	2/21/2024
5	39	2/21/2024		4/19/2024		58
5	24	2/21/2024		3/4/2024		12

At the end of each month, I need to calculate the time to ship for each order that hasn't been completely shipped I appreciate your patience and thank you for your time! by the end of the month in question. For example, Order 1 was place on 1/03 and it was not completely shipped out by the end of January so it had a pending age of 28 days at the end of January and then 57 days at the end of February and so on. If an order is completely shipped, then I am no longer interested in it but it should appear in the calculations for the months prior to its final ship date.  

Using the data table above, the table below might help explain what I'm trying to do. I am interested in the average for each month.

	Order Age (Days)	Order Age (Days)		Order Age (Days)		Order Age (Days)		Order Age (Days)
Order Number	1/31/2024	2/29/2024		3/31/2024		4/30/2024		5/31/2024
1	28	57		88		118		149
2	18	47		78		108
3		25		56		86
4		23		54		118
5		8		39		69		100
Average	23	32		63		99.8		124.5