Days from Ticket start date to PO receipt date: filtered by fully received PO lines

[Anonymous]

Hello ! First post here and I'm happy to be diving in to this community 🙂 

My scenario... 

Order requests are processed through a ticketing system. This system has an 'Issue Date' field that captures when the order was first submitted.

Ticketing Dimension looks like this: 

Issue ID	Issue Date
12345	2/4/2022
13579	3/10/2022
24680	3/15/2022
59731	4/6/2022

Ordered items are recorded in another order tracking tool. As are the receipts of those items when they arrive. I am working with PO line level details from the order tracking tool.

PO Lines Fact Table looks like this: 

PO Number	Line#	Receipt Status	Receipt Date	First Receipt Date	Last Receipt Date	Issue ID
123456789	1	Fully Received	2/10/2022	2/10/2022	4/6/2022	12345
123456789	2	Fully Received	3/15/2022	2/10/2022	4/6/2022	12345
123456789	3	Fully Received	4/6/2022	2/10/2022	4/6/2022	12345
222222222	1	Partially Received	2/10/2022	2/10/2022	2/10/2022	13579
222222222	2	Partially Received				13579
333333333	1	Fully Received	3/10/2022	3/10/2022	3/10/2022	24680
444444444	1	Pending Receipt				59731

They are connected on Issue ID with the ticketing dimension being one (1) and the PO Lines fact table being many (*). 

Not sure if relevant, but there are two date tables: one attached to the ticketing dimension and one attached to the fact table. 

I want to capture the order cycle time for completed orders only.

This means I want to get the DATEDIFF WHERE POs are showing in a "Fully Received" status only.

Something like:

DATEDIFF(

'Ticketing'[Issue Date],

'Order Tracking Tool'[Last Receipt Date],

DAY

)

WHERE 'Order Tracking Tool'[Receipt Status] = "Fully Received"

Looking at other resources I believe I may need to store a tmp POLines table filtered to Fully Received; and then use this to get the DATEDIFF. But sadly my attempts have all failed...  

I started with something like this, but obviously need 'POLines'[Last Receipt Date]) = MAX('POLines'[Last Receipt Date]).

CALCULATE(

DATEDIFF ( RELATED ( 'Ticketing'[Issue Date]), 'POLines'[Last Receipt Date] ),

FILTER(

POLines,
POLines[ReceiptStatus] = "Fully Received"

)

)

Accepting all input and advice 🙂 

----

I'm only looking for assistance with above; but it may help to understand my end goal - which is to use it in a measure like this: 

DIVIDE

(

SUM(Days from Issue Date to PO Receipt date of fully received POs),

SUM(Total requests received in that time period) 

)

Many thanks in advance ! If I'm missing anything please lmk ! 

[Anonymous · Accepted solution]

Hi @Anonymous ,

I think you can try this code to calcualte the datediff by filter.

DateDiff =
VAR _SUMMARIZE =
    SUMMARIZE (
        'Ticketing Dimension',
        'Ticketing Dimension'[Issue ID],
        "Datediff",
            VAR _MAX_Last_Receipt_Date =
                CALCULATE (
                    MAX ( 'PO Lines'[Last Receipt Date] ),
                    FILTER ( 'PO Lines', 'PO Lines'[Receipt Status] = "Fully Received" )
                )
            RETURN
                DATEDIFF (
                    MAX ( 'Ticketing Dimension'[Issue Date] ),
                    _MAX_Last_Receipt_Date,
                    DAY
                )
    )
RETURN
    SUMX ( _SUMMARIZE, [Datediff] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.