topic Re: Calculate Average Per Month for Open Orders in DAX Commands and Tips

Calculate Average Per Month for Open Orders

Seif949 — Wed, 03 Jul 2024 15:33:05 GMT

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

Re: Calculate Average Per Month for Open Orders

Anonymous — Thu, 04 Jul 2024 01:30:28 GMT

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

Re: Calculate Average Per Month for Open Orders

Seif949 — Tue, 23 Jul 2024 16:35:37 GMT

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