cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Arial12 Frequent Visitor
Frequent Visitor

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

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.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
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

 

daysdiff.png

Arial12 Frequent Visitor
Frequent Visitor

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

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,

 

3 REPLIES 3
Super User
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
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

 

daysdiff.png

Arial12 Frequent Visitor
Frequent Visitor

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

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,

 

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 397 members 3,861 guests
Please welcome our newest community members: