cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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

Hello Expert,

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

## Re: Date difference between concecutive order date and ship date for each customer

@Arial12

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

Frequent Visitor

## Re: Date difference between concecutive order date and ship date for each customer

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,

3 REPLIES 3
Super User

## Re: Date difference between concecutive order date and ship date for each customer

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 )```
Super User

## Re: Date difference between concecutive order date and ship date for each customer

@Arial12

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

Frequent Visitor

## Re: Date difference between concecutive order date and ship date for each customer

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,

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 416 members 4,361 guests
Recent signins: