Duration Since Previous TimeStamp by Person and Category Measure

[itsme]

I need a measure that will give me the duration since the previous timestamp for a particular person and category in the dataset. Below, I have a sample of the dataset. If it's the first timestamp for the given category and person, the duration should be blank or 0.

I figured it out using a calculated column for "Last TimeStamp", but I would prefer to only use measures as this dataset will have about 1M rows and I don't want more columns.

[amitchandak · Accepted solution]

@itsme , Try a new column

New column =
var _maxx(filter(Table, [Name] = earlier([Name]) && [Category] =earlier([Category]) && [datetimestamp] < earlier([datetimestamp])), [datetimestamp])
return
[datetimestamp] -_max

[v-yueyunzh-msft · Accepted solution]

Hi, @itsme

According to your description, you want to group according to [name] and [category], find the duration of previous timestamp, and you want to use measure to realize. Right?

Here are the steps you can follow：

(1)This is my test data:

(2)We can create a measure: “Duration”:

Duration =
VAR _cuurent_name =
    SELECTEDVALUE ( 'Test'[Name] )
VAR _current_category =
    SELECTEDVALUE ( 'Test'[Category] )
VAR _current_datetime =
    SELECTEDVALUE ( 'Test'[DateTimeStamp] )
VAR _pre_date =
    MAXX (
        FILTER (
            ALL ( 'Test' ),
            'Test'[Name] = _cuurent_name
                && 'Test'[Category] = _current_category
                && 'Test'[DateTimeStamp] < _current_datetime
        ),
        [DateTimeStamp]
    )
VAR _duration =
    VALUE ( _current_datetime - _pre_date )
RETURN
    IF (
        _pre_date = BLANK (),
        BLANK (),
        TRUNC ( _duration ) & ":"
            & FORMAT ( _duration, "hh:mm:ss" )
    )

(3)We put the measure [Duration] and other fields we need in the table, then we can meet your need:

If this method can't meet your requirement, can you share some specific input and output sample data?We can better understand the problem and help you.

Duration_Test.pbix 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it...

[itsme]

Thanks @v-yueyunzh-msft, this does work! 

[amitchandak · Accepted solution]

@itsme , Try a new column

New column =
var _maxx(filter(Table, [Name] = earlier([Name]) && [Category] =earlier([Category]) && [datetimestamp] < earlier([datetimestamp])), [datetimestamp])
return
[datetimestamp] -_max

[itsme]

Hi @amitchandak, thanks for your reply. Do you know how to do this as a measure only without creating new columns?