Calculating DateDiff and Days Since

[RUSA]

I need to calculate the number of days since the last order date for a set of customers in 2 ways.

1. "Days Since Last Order". The days between the PeriodEndDate on the current row and the PeriodEndDate of the most previous period in which the same customer made an order.

2. "Days Since Last Order From Today". The days between the maximum PeriodEndDate that the customer appears in the table and Today.

Attached is an attempted workflow. I wasn't able to get DateTimeDiff() to work at all, so I'm guessing there is a lot of operator error here.

CustomerID	OrderCount	Qty	Revenue	PeriodEndDate	DESIRED Days Since Last Order	DESIRED  Max_OrderPeriodEnd	DESIRED Days Since Last Order From Today
100	2	4	60	1/28/2018	0	4/26/2018	843
100	2	4	60	2/25/2018	28	4/26/2018	843
100	5	10	150	4/26/2018	60	4/26/2018	843
150	0	4	60	2/25/2018	0	4/26/2018	843
150	5	10	150	4/26/2018	60	4/26/2018	843
200	3	6	90	1/28/2018	0	1/28/2018	931
300	5	10	150	1/28/2018	0	1/28/2018	931
300	2	4	60	2/25/2018	28	2/25/2018	903
300	0	10	150	4/26/2018	60	2/25/2018	903
400	1	2	30	1/28/2018	0	4/26/2018	843
400	3	6	90	2/25/2018	28	4/26/2018	843
400	2	4	60	4/26/2018	60	4/26/2018	843
500	4	8	120	1/28/2018	0	4/26/2018	843
500	4	8	120	2/25/2018	28	4/26/2018	843
500	5	10	150	4/26/2018	60	4/26/2018	843
600	1	2	30	1/28/2018	0	4/26/2018	843
600	4	8	120	4/26/2018	88	4/26/2018	843
650	0	8	120	2/25/2018	0	0	0
700	5	10	150	1/28/2018	0	2/25/2018	903
700	3	6	90	2/25/2018	28	2/25/2018	903
800	4	8	120	1/28/2018	0	4/26/2018	843
800	3	6	90	4/26/2018	88	4/26/2018	843
900	0	10	150	2/25/2018	0	4/26/2018	843
900	4	8	120	4/26/2018	60	4/26/2018	843
1000	1	2	30	1/28/2018	0	2/25/2018	903
1000	5	10	150	2/25/2018	28	2/25/2018	903

[wwatson · Accepted solution]

you'll need to convert your period end date into a date type using a datetimeparse tool or a %m/%d/%Y. Then datetimediff should work.