If the next row is in timespan within 30 minutes of the current row then increment count +1 else reset count.
Below you can see the desired output:
Solved! Go to Solution.
Hi @Niels_IRD ,
Please try:
First add an index column:
Then create a calculated column:
Column =
VAR _a =
CALCULATE (
MAX ( 'Table'[Date] ) + MAX ( 'Table'[Time] ),
FILTER ( 'Table', [Index] = EARLIER ( 'Table'[Index] ) - 1 )
)
VAR _b = [Date] + [Time]
VAR _c =
ADDCOLUMNS (
'Table',
"a",
CALCULATE (
MAX ( 'Table'[Date] ) + MAX ( 'Table'[Time] ),
FILTER ( 'Table', [Index] = EARLIER ( 'Table'[Index] ) - 1 )
),
"b", [Date] + [Time]
)
VAR _diff =
ADDCOLUMNS ( _c, "Diff", DATEDIFF ( [a], [b], MINUTE ) )
VAR _maxindex =
MINX (
FILTER ( _diff, [Diff] > 30 && [Index] >= EARLIER ( 'Table'[Index] ) ),
[Index]
)
VAR _minindex =
MAXX (
FILTER ( _diff, [Diff] > 30 && [Index] <= EARLIER ( 'Table'[Index] ) ),
[Index]
) + 0
VAR _filtertable =
FILTER ( 'Table', [Index] >= _minindex && [Index] <= _maxindex )
VAR _count =
COUNTROWS ( FILTER ( _filtertable, [Index] <= EARLIER ( 'Table'[Index] ) ) )
RETURN
_count
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Niels_IRD ,
Please try:
First add an index column:
Then create a calculated column:
Column =
VAR _a =
CALCULATE (
MAX ( 'Table'[Date] ) + MAX ( 'Table'[Time] ),
FILTER ( 'Table', [Index] = EARLIER ( 'Table'[Index] ) - 1 )
)
VAR _b = [Date] + [Time]
VAR _c =
ADDCOLUMNS (
'Table',
"a",
CALCULATE (
MAX ( 'Table'[Date] ) + MAX ( 'Table'[Time] ),
FILTER ( 'Table', [Index] = EARLIER ( 'Table'[Index] ) - 1 )
),
"b", [Date] + [Time]
)
VAR _diff =
ADDCOLUMNS ( _c, "Diff", DATEDIFF ( [a], [b], MINUTE ) )
VAR _maxindex =
MINX (
FILTER ( _diff, [Diff] > 30 && [Index] >= EARLIER ( 'Table'[Index] ) ),
[Index]
)
VAR _minindex =
MAXX (
FILTER ( _diff, [Diff] > 30 && [Index] <= EARLIER ( 'Table'[Index] ) ),
[Index]
) + 0
VAR _filtertable =
FILTER ( 'Table', [Index] >= _minindex && [Index] <= _maxindex )
VAR _count =
COUNTROWS ( FILTER ( _filtertable, [Index] <= EARLIER ( 'Table'[Index] ) ) )
RETURN
_count
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Niels_IRD ,a new column
countx(filter(Table, [Date] =earlier([Date]) && [Time] <= earlier([Time]) ), [Date])
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
125 | |
75 | |
65 | |
56 | |
55 |
User | Count |
---|---|
199 | |
104 | |
88 | |
79 | |
77 |