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.
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])
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 |
---|---|
107 | |
98 | |
78 | |
65 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |