Summarize Date/time to Date and Lookup within same table

[shadow7]

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

[Anonymous · Accepted solution]

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

[lbendlin]

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?