Date difference between concecutive order date and ship date for each customer

[Arial12]

Hello Expert,

please am in need of your help

custID                   Orderdt.                  Shipdt.           daysbetweenorder(first shipdt - second (next orderdt) orderdt

123                        1/1/2014                 1/3/2014        3 

123                        1/6/2014                 1/10/2014      2

123.                       1/12/2014               1/14/2014       Null 

234                         1/1/2014                1/4/2014         28       

234.                        2/1/2014                 2/2/2014.       Null

for the first row each customer , datediff 1/3/2014 , 1/6/2014 = 3 days

same customer second row, datediff    1/10/2014,  1/12/2014 = 2 days

same customer third row ,  for now is the last shipdt so that can be null or blank until that same customer orders it again and I want this to be dynamic so that it will auto calculate the difference for every customer.

[Zubair_Muhammad · Accepted solution]

@Arial12

it works with sample data.... but if the days are overlapping... it might not work

[Zubair_Muhammad]

Hi @Arial12

Try this calculated column

Days between orders =
VAR CurrentRowOrderDate = TableName[Orderdt.]
VAR NextOrder =
    CALCULATE (
        MIN ( TableName[Orderdt.] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[custID] ),
            TableName[Orderdt.] > CurrentRowOrderDate
        )
    )
RETURN
    DATEDIFF ( TableName[Shipdt.], NextOrder, DAY )

[Zubair_Muhammad · Accepted solution]

@Arial12

it works with sample data.... but if the days are overlapping... it might not work

[Arial12 · Accepted solution]

Thank you Zubair_Muhammad.

For any overlapping, i created a new column which give me nextdt for each row 

NextDT=CALCULATE(MIN(Table[Orderdt]),FILTER(Table,Table[Custid]=EARLIER(Table[Custid]) && Table[Orderdt] > EARLIER(table[Orderdt])))

and comaparion between the dates orderdt and NextDT and getting datediff.

Thanks,