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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tharveysa
Frequent Visitor

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

Document No_Line No_No_DescriptionResolution CodeResolved DateResolved UserNameOrderIndex
1405021400001clean & strip coatingsCOMPLETE1/10/2018 0:00SHARVEY0
1405021700001corosion controlCOMPLETE1/10/2018 0:00SHARVEY0
1405021100001disassemble & clean hardwareCOMPLETE1/11/2018 0:00SHARVEY0
1405021200001ndt all hardwareCOMPLETE1/11/2018 0:00SHARVEY0
1405021600001paintCOMPLETE1/11/2018 0:00SHARVEY0
140502900001teardownCOMPLETE1/10/2018 0:00SHARVEY1
1405021000001inspect & assemble hardwareCOMPLETE1/12/2018 0:00SHARVEY2
1405021300001ndt main componentCOMPLETE1/12/2018 0:00SHARVEY2
1405021500001quote work orderCOMPLETE1/12/2018 0:00SHARVEY3
1405021800001final assembly wheel ovh (no disk)COMPLETE1/15/2018 0:00SHARVEY4
1405021900001functional test wheel ovh (no disk)COMPLETE1/15/2018 0:00SHARVEY5
1405022000001final ins & mntc release wheel ovh (no disk)COMPLETE1/15/2018 0:00SHARVEY6

 

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.

1 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
    )

1e.PNG

Thanks,
Xi Jin.

View solution in original post

5 REPLIES 5
v-xjiin-msft
Solution Sage
Solution Sage

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.

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 TimeCustomer DaysTurn Time Minus Customer Days    
140502725    
1405051046    
14055511011    

 

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.

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
    )

1e.PNG

Thanks,
Xi Jin.

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

 

THANK YOU SO MUCH! @v-xjiin-msft

 

 

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.