Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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

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 IDIssue Date
123452/4/2022
135793/10/2022
246803/15/2022
597314/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 NumberLine#Receipt StatusReceipt DateFirst Receipt DateLast Receipt DateIssue ID
1234567891Fully Received2/10/20222/10/20224/6/202212345
1234567892Fully Received3/15/20222/10/20224/6/202212345
1234567893Fully Received4/6/20222/10/20224/6/202212345
2222222221Partially Received2/10/20222/10/20222/10/202213579
2222222222Partially Received   13579
3333333331Fully Received3/10/20223/10/20223/10/202224680
4444444441Pending 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 ! 

 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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.

RicoZhou_0-1649667281423.png

 

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.

 

View solution in original post

1 REPLY 1
v-rzhou-msft
Community Support
Community Support

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.

RicoZhou_0-1649667281423.png

 

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.

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors