# Calculating time between transactions

• ### Question

• Hi,

I am quite new to DAX formulas and have been struggeling a while with the following problem. The set I am working with contains StoreID, CashierID, TransactionID, Date, Time, ItemID and I want to calculate the time range between each transaction.

This is an example of the data set I have. My set contains around 3 million rows.

 StoreID CashierID TransactionID Date Time ItemID Store 1 Cashier 1 1 01.01.2018 08:00:00 1 Store 1 Cashier 1 1 01.01.2018 08:00:00 2 Store 1 Cashier 1 1 01.01.2018 08:00:00 3 Store 1 Cashier 1 1 01.01.2018 08:00:00 4 Store 1 Cashier 1 1 01.01.2018 08:00:00 5 Store 1 Cashier 1 2 01.01.2018 08:02:00 1 Store 1 Cashier 1 2 01.01.2018 08:02:00 6 Store 1 Cashier 1 2 01.01.2018 08:02:00 8 Store 1 Cashier 1 2 01.01.2018 08:02:00 9 Store 1 Cashier 1 2 01.01.2018 08:02:00 7 Store 1 Cashier 2 3 01.01.2018 08:02:10 1 Store 1 Cashier 2 3 01.01.2018 08:02:10 3 Store 1 Cashier 2 4 01.01.2018 08:02:50 6 Store 1 Cashier 2 4 01.01.2018 08:02:50 9 Store 2 Cashier 1 5 01.01.2018 07:00:00 1 Store 2 Cashier 1 5 01.01.2018 07:00:00 2 Store 2 Cashier 1 5 01.01.2018 07:00:00 3 Store 2 Cashier 1 5 01.01.2018 07:00:00 4 Store 2 Cashier 1 5 01.01.2018 07:00:00 5 Store 2 Cashier 1 6 01.01.2018 07:01:30 1 Store 2 Cashier 1 6 01.01.2018 07:01:30 6 Store 2 Cashier 1 6 01.01.2018 07:01:30 8 Store 2 Cashier 1 6 01.01.2018 07:01:30 9 Store 2 Cashier 1 6 01.01.2018 07:01:30 7

This is the desired result where Time Range is the time between each transaction:

 StoreID CashierID TransactionID Date Time Time Range Store 1 Cashier 1 1 01.01.2018 08:00:00 00:02:00 Store 1 Cashier 1 2 01.01.2018 08:02:00 Store 1 Cashier 2 3 01.01.2018 08:02:10 00:00:40 Store 1 Cashier 2 4 01.01.2018 08:02:50 Store 2 Cashier 1 5 01.01.2018 07:00:00 00:01:30 Store 2 Cashier 1 6 01.01.2018 07:01:30

To get to this result I first created a calculated field/measure, TransactionTime = MINX(Data;[Time]).
I then ranked each transaction in a new measure,

TransactionRank = RANKX(
FILTER(all(Data[TransactionID]),
[TransactionTime]),[TransactionTime],,ASC;Dense
)

So far so good. But I have not found a solution how to find the next transaction time for then to subtract this with the current transaction time.

I have thought the solution could be something like the example below but I don't know what to put in the "???",
CALCULATE(
[TransactionTime],
FILTER(
Data,
[TransactionRank] = ???
)
)

I would apprecate if someone could help me with this!

Thank you,

Friday, January 19, 2018 10:29 AM

In this scenario, I would suggest you create calculated columns Rank,NextTime and TimeRange as below:

```=
RANKX (
FILTER (
Table1,
Table1[StoreID] = EARLIER ( Table1[StoreID] )
&& Table1[CashierID] = EARLIER ( Table1[CashierID] )
),
Table1[TransactionID],
,
ASC,
DENSE
)```

```=
LOOKUPVALUE (
Table1[Time],
Table1[StoreID], Table1[StoreID],
Table1[CashierID], Table1[CashierID],
Table1[Rank], Table1[Rank] + 1
)```

```=
IF ( ISBLANK ( Table1[TimeRange] ), BLANK (), Table1[TimeRange] - Table1[Time] )```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

• Marked as answer by Monday, January 22, 2018 9:26 AM
Monday, January 22, 2018 4:14 AM

### All replies

• Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
Easier with PQ.
PP and PQ are well integrated in 2016.
http://www.mediafire.com/file/w6hz7aub5c47prc/01_19_18.xlsx
http://www.mediafire.com/file/6z1s8b54ajebao4/01_19_18.pdf

Friday, January 19, 2018 11:34 PM

In this scenario, I would suggest you create calculated columns Rank,NextTime and TimeRange as below:

```=
RANKX (
FILTER (
Table1,
Table1[StoreID] = EARLIER ( Table1[StoreID] )
&& Table1[CashierID] = EARLIER ( Table1[CashierID] )
),
Table1[TransactionID],
,
ASC,
DENSE
)```

```=
LOOKUPVALUE (
Table1[Time],
Table1[StoreID], Table1[StoreID],
Table1[CashierID], Table1[CashierID],
Table1[Rank], Table1[Rank] + 1
)```

```=
IF ( ISBLANK ( Table1[TimeRange] ), BLANK (), Table1[TimeRange] - Table1[Time] )```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

• Marked as answer by Monday, January 22, 2018 9:26 AM
Monday, January 22, 2018 4:14 AM
• Thank you, Willson Yuan!

That worked perfectly! I also had to add Date as part of the calculated column Rank.