DATEDIFF between 2 rows aggregating by 2 columns DAX

1
votes

I have a big data set with the structure as shown below.

Operation User Timestamp Elapsed time
12 1 2018-01-03 11:19:02 AM
12 1 2018-01-03 12:34:02 PM
12 1 2018-01-04 8:34:02 AM
12 2 2018-02-03 9:34:02 AM
12 2 2018-02-03 11:12:42 AM
12 3 2018-02-03 12:12:00 PM
15 1 2018-01-02 9:22:32 AM
15 1 2018-01-02 9:25:32 AM
15 2 2018-01-02 9:25:32 AM

The goal is to form the column "Elapsed Time" using DAX and PowerBI. The column shows the difference/duration between the current timestamp and previous timestamp for the same user and the same operation.

I've tried something along the lines of:

Elapsed time =
DATEDIFF (
    CALCULATE (
        MAX ( data[Timestamp] ),
        ALLEXCEPT ( data, data[Operation], data[User] ),
        data[Timestamp] < EARLIER ( data[Timestamp] )
    ),
    data[Timestamp],
    MINUTE
)

`

But it complains about a single value for column 'Timestamp' in table 'data' cannot be determined. this can happen when a measure formula refers to a column that contains many values without specifying an aggregator such as min, max, count, or sum to get a single result.

I'm very new to DAX, so I'd appreciate any help.

2
powerbidaxpowerquery

2 Answers

1
votes

Since the 'Table'[Operation] and 'Table'[User] of the current row are to be used as filter, a very simple approach might just use CALCULATE to trigger the context transition, transforming the current row context to the corresponding filter context, and then to replace the filer over 'Table'[Timestamp] to be less than the current Timestamp, previously saved to a variable. The context transition automatically sets the correct filters over 'Table'[Operation] and 'Table'[User]

Elapsed time =
VAR CurrentTimestamp = 'Table'[TimeStamp]
RETURN
    DATEDIFF (
        CALCULATE ( MAX ( 'Table'[Timestamp] ), 'Table'[Timestamp] < CurrentTimestamp ),
        CurrentTimestamp,
        MINUTE
    )
0
votes

Typing on the mobile, so apologies for possible errors. Assuming this is a calculated column:

Elapsed time =
DATEDIFF (
    CALCULATE (
        MAX ( Table[Timestamp] ),
        FILTER (
            Table,
            Table[User] = EARLIER ( Table[User] )
                && Table[Operation] = EARLIER ( Table[Operation] )
                && Table[Timestamp] < EARLIER ( Table[Timestamp] )
        )
    ),
    Table[Timestamp],
    MINUTE
)

Where Table is your table name.

There surely are now ways to do that, do apologies for non optimal approach.