topic Re: Duration Since Previous TimeStamp by Person and Category Measure in DAX Commands and Tips

Duration Since Previous TimeStamp by Person and Category Measure

itsme — Thu, 01 Sep 2022 21:59:39 GMT

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.

Re: Duration Since Previous TimeStamp by Person and Category Measure

amitchandak — Fri, 02 Sep 2022 03:29:42 GMT

itsme , Try a new column

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

Re: Duration Since Previous TimeStamp by Person and Category Measure

itsme — Fri, 02 Sep 2022 16:49:07 GMT

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

Re: Duration Since Previous TimeStamp by Person and Category Measure

v-yueyunzh-msft — Tue, 06 Sep 2022 03:59:16 GMT

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 more quickly.

Re: Duration Since Previous TimeStamp by Person and Category Measure

itsme — Wed, 07 Sep 2022 22:48:26 GMT

Thanks v-yueyunzh-msft, this does work!