Summarize Date/time to Date and Lookup within same table

shadow7 — Fri, 22 Dec 2023 21:46:51 GMT

Hello Experts,

I need to summarize values at Date/time to Date and Lookup within same table based on Ship Date. As I am joining this data based on SHIPDT, would like to get the Other QTY details against SHIPDT. Tried Lookup/related/Treatas but no luck. Appreciate any help with this.

Thanks in advance.

Actual Data: Expected Data:

CREATEDT	Order ID	PROCESSING_QTY	SHIPDT	Ordered Pairs	PROCESSEDTS	PROCESSED_QTY
12/10/2023 0:00	ORDER-1		12/17/2023 0:00	1585
12/11/2023 0:00	ORDER-2		12/18/2023 0:00	3773
12/12/2023 0:00	ORDER-3		12/19/2023 0:00	5083
12/13/2023 0:00	ORDER-4		12/20/2023 0:00	8800
12/14/2023 0:00	ORDER-5		12/21/2023 0:00	3610
12/15/2023 0:00	ORDER-6	193
12/16/2023 0:00	ORDER-7	622
12/18/2023 0:00	ORDER-8	831
12/19/2023 0:00	ORDER-9	4243
12/20/2023 0:00	ORDER-10	182
12/21/2023 0:00	ORDER-11	420
12/22/2023 0:00	ORDER-12	7417
12/23/2023 0:00	ORDER-13				12/18/2023 6:37	252
12/24/2023 0:00	ORDER-14				12/18/2023 6:48	702
12/25/2023 0:00	ORDER-15				12/18/2023 7:29	0
12/26/2023 0:00	ORDER-16				12/18/2023 8:53	1
12/27/2023 0:00	ORDER-17				12/18/2023 8:53	1
12/28/2023 0:00	ORDER-18				12/18/2023 8:53	1
12/29/2023 0:00	ORDER-19				12/18/2023 9:23	1
12/30/2023 0:00	ORDER-20				12/18/2023 9:38	0
12/31/2023 0:00	ORDER-21				12/18/2023 9:42	0
1/1/2024 0:00	ORDER-22				12/18/2023 11:29	0
1/2/2024 0:00	ORDER-23				12/18/2023 11:37	0
1/3/2024 0:00	ORDER-24				12/18/2023 11:42	4
1/4/2024 0:00	ORDER-25				12/18/2023 11:43	2
1/5/2024 0:00	ORDER-26				12/18/2023 11:43	2
1/6/2024 0:00	ORDER-27				12/18/2023 11:43	1
1/7/2024 0:00	ORDER-28				12/18/2023 11:44	3
1/8/2024 0:00	ORDER-29				12/18/2023 11:44	1
1/9/2024 0:00	ORDER-30				12/18/2023 11:44	6
1/10/2024 0:00	ORDER-31				12/18/2023 11:44	3
1/11/2024 0:00	ORDER-32				12/19/2023 14:46	12
1/12/2024 0:00	ORDER-33				12/19/2023 14:55	8
1/13/2024 0:00	ORDER-34				12/19/2023 15:05	1
1/14/2024 0:00	ORDER-35				12/19/2023 15:44	1
1/15/2024 0:00	ORDER-36				12/19/2023 22:47	4
1/16/2024 0:00	ORDER-37				12/19/2023 22:50	1

Re: Summarize Date/time to Date and Lookup within same table

lbendlin — Sun, 24 Dec 2023 23:57:54 GMT

Looks like your "Actual Data" may be placing multiple tables on the same Excel (?) sheet? What's the importance of the Order ID column? Where does the "Ordered Pairs" term come from? Why is PROCESSING_QTY empty for the first orders? Should PROCESSEDTS and PROCESSED_QTY be treated as its own table?

Re: Summarize Date/time to Date and Lookup within same table

Anonymous — Mon, 25 Dec 2023 05:47:33 GMT

Hi shadow7 ,

You can create a measure or calculated column as below to get it:

Measure = VAR _shipdt = SELECTEDVALUE ( 'Table'[SHIPDT] ) RETURN CALCULATE ( SUM ( 'Table'[PROCESSED_QTY] ), FILTER ( ALLSELECTED ( 'Table' ), DATEVALUE ( 'Table'[PROCESSEDTS] ) = DATEVALUE ( _shipdt ) ) )

Best Regards

topic Re: Summarize Date/time to Date and Lookup within same table in DAX Commands and Tips

Summarize Date/time to Date and Lookup within same table

Re: Summarize Date/time to Date and Lookup within same table

Re: Summarize Date/time to Date and Lookup within same table