locked
Calculating time between transactions RRS feed

  • 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,

    Mads

      


    Friday, January 19, 2018 10:29 AM

Answers

  • Hi Mads,

    Thanks for your question.

    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 Warrowic 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
  • Hi Mads,

    Thanks for your question.

    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 Warrowic 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.

    Mads

    Monday, January 22, 2018 9:26 AM