Datediff with filter on doc number by -1 order index group

[tharveysa]

Document No_	Line No_	No_	Description	Resolution Code	Resolved Date	Resolved UserName	OrderIndex
140502	140000	1	clean & strip coatings	COMPLETE	1/10/2018 0:00	SHARVEY	0
140502	170000	1	corosion control	COMPLETE	1/10/2018 0:00	SHARVEY	0
140502	110000	1	disassemble & clean hardware	COMPLETE	1/11/2018 0:00	SHARVEY	0
140502	120000	1	ndt all hardware	COMPLETE	1/11/2018 0:00	SHARVEY	0
140502	160000	1	paint	COMPLETE	1/11/2018 0:00	SHARVEY	0
140502	90000	1	teardown	COMPLETE	1/10/2018 0:00	SHARVEY	1
140502	100000	1	inspect & assemble hardware	COMPLETE	1/12/2018 0:00	SHARVEY	2
140502	130000	1	ndt main component	COMPLETE	1/12/2018 0:00	SHARVEY	2
140502	150000	1	quote work order	COMPLETE	1/12/2018 0:00	SHARVEY	3
140502	180000	1	final assembly wheel ovh (no disk)	COMPLETE	1/15/2018 0:00	SHARVEY	4
140502	190000	1	functional test wheel ovh (no disk)	COMPLETE	1/15/2018 0:00	SHARVEY	5
140502	200000	1	final ins & mntc release wheel ovh (no disk)	COMPLETE	1/15/2018 0:00	SHARVEY	6

Above is an example of my data.  I need to find out when the Quote Step(Orderindex 3) is signed off, the max date of the steps with orderindex of 2.  Each of the 2's can be signed off in any order and sometimes there is more than these 2 indexes of no 2's.  Scenario is that when the final #2 index is signed off, this is the "Quotable" date.  I need to find this date and then the difference between it and the Quote sign date. 

I am trying to quantify turn times less the "Quoting" or Customer days that it takes them to approve/deny.

The table that these lines belong have 1000's of lines all key grouped by the doc no.  The line no's are not always in numberical order because there are mods/other lines entered such as products and charges.  

If it helps, I have given each of the order index lines above 0-1,2,3..- "Dept" names and have also indexed those.  Each dept will also have their own turn times as well but is much easier with an indexed field and if I can get the answer to this issue, it will provide a solution for this tt as well.

Thanks in advance,

Sam.

Ninja edit some errors.

[v-xjiin-msft]

Hi @tharveysa,

Sorry but I'm not quite understand your requirement.

=> the max date of the steps with orderindex of 2.

For the max date, did you mean the column Resolved Date?

=> Each of the 2's can be signed off in any order and sometimes there is more than these 2 indexes of no 2's.

How do we know which 2 is signed off? What's th logic?

=> I need to find this date and then the difference between it and the Quote sign date. 

What is the Quote sign date? Did you mean the Resolved Date of Quote Step(Orderindex 3)?

Since you have already shared us the sample data. Pleasa also share us your desired result based on this sample data. So that we'll get a right direction.

Thanks,
Xi Jin.

[tharveysa]

Yes, the max resolved date.  Basically, depts sign off these steps by inserting "COMPLETE" on the res code field.  Logic prevents quote from being signed off before all order index's lower than it (2's).  I need to find the last line of 2's that was completed which is why I'm after the max resolved date of the 2's.  This would indicate that it was time for quote on the max date of the 2's being signed.  When the Quote step is signed off, the customer has approved the quote which would mean the difference between the max resolved date and the completion date of the quote step is the time that the job was in the customer's hands-meaning it should not be counted as production time/turn time of the job.  (Definition of turn time in our eyes is the time we receive it to the time the customer receives it back subtracting any time they had control)

I am not presenting this data as a table and merely just measuring production levels but I'll try my best to explain.

I want to measure the time between line no 150000 and the last signed date of any orderindex of 2.  If I would try and do this in my head I would want to do something like:

We'll call this table 'ServLine'

Customer Days = 
var prevRowDate = CALCULATE(MAX('ServLine'[Resolved Date]), Filter(GroupBy('ServLine'[Document No_]), ServLine'[OrderIndex] < 3))
return datediff(prevRowDate, 'ServLine'[Resolved Date], FILTER('ServLine','ServLine'[Description] "Contains?" SEARCH("quote work order"?) Or Filter(OrderIndex = 3), DAY)

I'm very new to dax and PBI and don't understand each filter/grouping conventions but I think it would end up being something like this.  I'd also probably want to make sure that Resolution Code = "Complete" and the res date is not 1/1/1753 or whatever defaults as blanks.

Also not sure on how to exactly do contains in text var.

In the end this will be presented in a table similar to this, or even as a total measure over all orders specified date:

Document No_	Turn Time	Customer Days	Turn Time Minus Customer Days
140502	7	2	5
140505	10	4	6
140555	11	0	11

Customer Days = Line 130000 or 100000's Resolved Date(Since both are adequette because both are orderindex 2 and both have max value) and datediff with Quote Step - DATEDIFF(1/12/2018,1/12/2018) = Customer Days.

Turn Time is done with different collumns not listed above.  This is the time the document was created till posting date(Shipped Date).  Like stated above, the customer takes time to approve/deny the quote which is what we are subtracting because it's not in our control.

Thanks for your time.

[v-xjiin-msft · Accepted solution]

Hi @tharveysa,

But DATEDIFF(1/12/2018,1/12/2018) should be 0. Why it is 2 in your desired result?

And based on your description, the expression should be like this:

Customer Days =
VAR Maxresolveddate =
    CALCULATE (
        MAX ( ServLine[Resolved Date] ),
        FILTER ( ServLine, ServLine[OrderIndex] < 3 ),
        ALLEXCEPT ( ServLine, ServLine[Document No_] )
    )
RETURN
    DATEDIFF (
        Maxresolveddate,
        CALCULATE (
            MAX ( ServLine[Resolved Date] ),
            FILTER ( ServLine, ServLine[Description] = "quote work order" ),
            ALLEXCEPT ( ServLine, ServLine[Document No_] )
        ),
        DAY
    )

Thanks,
Xi Jin.

[tharveysa]

This works as described, I don't care what anyone says!  You're the best!

THANK YOU SO MUCH! @v-xjiin-msft

[tharveysa]

Quick reply just before I try this code, I put some random dates and results in there which now looking back, is confusing.  Those results were thrown together without any merrit on accuracy or backed by any data which I apoligize.  I'm going to throw this in my report and see how it comes out.

Again, thanks for your time!