Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
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.
@itsme , Try a new column
New column =
var _maxx(filter(Table, [Name] = earlier([Name]) && [Category] =earlier([Category]) && [datetimestamp] < earlier([datetimestamp])), [datetimestamp])
return
[datetimestamp] -_max
Hi @amitchandak, thanks for your reply. Do you know how to do this as a measure only without creating new columns?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
27 | |
23 | |
15 | |
11 |
User | Count |
---|---|
76 | |
58 | |
46 | |
17 | |
12 |