Forum Discussion

RichardT_78's avatar
RichardT_78
Helper I
5 years ago
Solved

Time

Hi  I have used this code to determine the time difference between rows as a messure.    This looks at each row of data.    Time Difference Measure13 = VAR NextRow = CALCULATE ( MIN ( FAC[S...
General Comment
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hello @RichardT_78 ,

    You can update your measurement as below screenshot shown, please find more details in the attachment:

    Time Difference = 
VAR _nexindex =
    CALCULATE (
        MIN ( 'FAC'[Index] ),
        FILTER (
            ALLEXCEPT ( 'FAC', 'FAC'[Consist] ),
            'FAC'[Index] > MAX ( 'FAC'[Index] )
        )
    )
VAR _nexttime =
    CALCULATE (
        MAX ( 'FAC'[Start Date/Time] ),
        FILTER ( ALLEXCEPT ( 'FAC', 'FAC'[Consist] ), 'FAC'[Index] = _nexindex )
    )
RETURN
    DATEDIFF ( SELECTEDVALUE ( 'FAC'[Start Date/Time] ), _nexttime, SECOND ) + 0

    Time.JPG

    Best regards

RichardT_78's avatar
RichardT_78
Helper I
5 years ago

ConsistStart Date/Time
37743601/12/2020 13:08:51
37743601/12/2020 13:08:50
37715301/12/2020 13:06:32
37715301/12/2020 13:06:30
37740801/12/2020 13:04:51
37743601/12/2020 13:04:40
37743601/12/2020 13:04:39
37714001/12/2020 13:02:14
37714001/12/2020 13:02:12
37743601/12/2020 13:01:00
37743601/12/2020 13:00:59
37742401/12/2020 12:59:54
37742401/12/2020 12:59:51
37744701/12/2020 12:59:40
37710401/12/2020 12:59:36

377436 would be a good test as its spread over the table  

 

So are for example 100 consist codes, which all have a time stamp for each particular event.

When I use a slicer and select 377104 for example, it displays all events chronologically, but the messure fails. 

As you can see the messure works fine when you dont focus on a consist, but when i select a consist its rows are not consecutive.

 

Hope this helps , much appricated 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hello @RichardT_78 ,

You can update your measurement as below screenshot shown, please find more details in the attachment:

Time Difference = 
VAR _nexindex =
    CALCULATE (
        MIN ( 'FAC'[Index] ),
        FILTER (
            ALLEXCEPT ( 'FAC', 'FAC'[Consist] ),
            'FAC'[Index] > MAX ( 'FAC'[Index] )
        )
    )
VAR _nexttime =
    CALCULATE (
        MAX ( 'FAC'[Start Date/Time] ),
        FILTER ( ALLEXCEPT ( 'FAC', 'FAC'[Consist] ), 'FAC'[Index] = _nexindex )
    )
RETURN
    DATEDIFF ( SELECTEDVALUE ( 'FAC'[Start Date/Time] ), _nexttime, SECOND ) + 0

Time.JPG

Best regards