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
Solved! Go to 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.
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.
Thanks, it's working!
The EARLIER is what I'm missing 😯.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
119 | |
75 | |
65 | |
51 | |
51 |
User | Count |
---|---|
184 | |
104 | |
82 | |
79 | |
78 |