Filter all the lines that based on status

[ChristianDGreat]

Hello everyone.

I got stucked on this and need a bit of assistance.

So I have this table, and its connected using the OrderLine#

Order Number	OrderLine#	Sales
AORDER	1A	1000
AORDER	2B	1000
AORDER	1C	1000
BORDER	1CC	2000

OrderLine#	Status	Timestamp
1A	Inquiry	11/5/22 6:00 AM
1A	Booked	11/6/22 6:00 AM
1A	Shipped	11/7/22 6:00 AM
1A	Closed	11/8/22 6:00 AM
2B	Inquiry	11/9/22 6:00 AM
2B	Booked	11/10/22 6:00 AM
2B	Cancelled	11/12/22 6:00 AM
1C	Inquiry	11/13/22 6:00 AM
1C	Booked	11/14/22 6:00 AM
1C	Shipped	11/15/22 6:00 AM

The goal here is to only show the result of a status = closed

in this case

If I filter the Order Number = AORDER

It will only show this,

OrderLine#	DateDiff
1A	3

*Date Diff is the date difference between - 11/5/22 and 11/8/22 (from Inquiry to closed)

Again the goal is to show only the orderline# that is closed but need to consider all the historical of it.

1A - in our example this show because it has a closed status 

2B - this didnt show because it doesnt have a closed status

1C -  this didnt show because it doesnt have a closed status

[v-xiaosun-msft · Accepted solution]

Hi @ChristianDGreat ,

According to your description, here is my solution. Please follow these steps.

Create a calculated column.

datediff =
VAR _Closed =
    MAXX (
        FILTER (
            'Table (2)',
            [OrderLine#] = EARLIER ( [OrderLine#] )
                && [Status] = "Closed"
        ),
        [Timestamp]
    )
VAR _Inquiry =
    MAXX (
        FILTER (
            'Table (2)',
            [OrderLine#] = EARLIER ( [OrderLine#] )
                && [Status] = "Inquiry"
        ),
        [Timestamp]
    )
RETURN
    IF (
        _Closed <> BLANK ()
            && _Inquiry <> BLANK (),
        DATEDIFF ( _Inquiry, _Closed, DAY )
    )

Put it to the filter, and select "is not blank", then you will get the final output.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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

[ChristianDGreat]

Thanks, it's working!

The EARLIER is what I'm missing 😯.