Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

How to write specific time formula

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:

 

Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi Anonymous ,

     

    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.

     

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous ,a new column

     

    countx(filter(Table, [Date] =earlier([Date]) && [Time] <= earlier([Time]) ), [Date])

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi Anonymous ,

     

    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.