Calculating time -time between multiple events with time in one column

[WmUit]

Hi all

I tried the formula from this solution https://community.powerbi.com/t5/Desktop/Calculated-Column-time-between-events-in-different-rows/m-p... on my situation because it looks like the same intention, but when adapting the formula it only worked partly. The weird thing is that once in a while it calculates the time between the events, but most of the time it doesn't.(in the table shown below It only calculated the last event).

My situation is show below. I want to calculate the time between, when an event occurs ‘’Gekomen’’ and when the events disappears ‘’Gegaan’’, shown in the Status row. The Events have an ID which is connected by an type of event (Status) , so it can occur multiple times, I want to know the time between the status (''gegaan'' & ''gekomen''). (the difficult part is that all the event values are appearing trough each other and  the events can appear and disappear at the same datetime, also the events can happen multiple times in a short time.  I hope you can help me!

Status                   Date and Time                   ID           Index    Duration

Gegaan                1-2-2021 09:34                  10           7            

Gegaan                1-2-2021 09:34                  637         7            

Gegaan                1-2-2021 09:34                  641         3            

Gegaan                1-2-2021 09:34                  641         3            

Gegaan                1-2-2021 09:35                  637         9            

Gegaan                1-2-2021 09:35                  10           9            

Gegaan                1-2-2021 09:37                  635         1            

Gegaan                1-2-2021 09:37                  626         3            

Gekomen             1-2-2021 09:37                  635         1            

Gekomen             1-2-2021 09:37                  635         1            

Gegaan                 1-2-2021 09:37                  635         1            

Gegaan                 1-2-2021 09:37                  626         3            

Gekomen              1-2-2021 09:37                  655         1            

Gegaan                 1-2-2021 09:37                  655         1            

Gekomen              1-2-2021 09:38                  656         1            

Gegaan                 1-2-2021 09:39                  656         2             1

(The data shown is just as refference for the real data)

I used the formula from the post in the link. 

Thanks for helping me!

[Anonymous]

Hi @WmUit ,

You can create a measure as below:

Duration =
VAR _Gekomen =
    CALCULATE (
        MAX ( 'Data'[Date and Time] ),
        FILTER (
            ALLSELECTED ( 'Data' ),
            'Data'[ID] = SELECTEDVALUE ( 'Data'[ID] )
                && 'Data'[Status] = "Gekomen"
        )
    )
VAR _Gegaan =
    CALCULATE (
        MIN ( 'Data'[Date and Time] ),
        FILTER (
            ALLSELECTED ( 'Data' ),
            'Data'[ID] = SELECTEDVALUE ( 'Data'[ID] )
                && 'Data'[Status] = "Gegaan"
                && 'Data'[Date and Time] > _Gekomen
        )
    )
RETURN
    DATEDIFF ( _Gekomen, _Gegaan, MINUTE )

If the above one is not working, please provide your expected result with more details. Thank you.

Best Regards

[WmUit]

Hello @Anonymous , Thank you for replying, The above is not working. To explain my expected result I made an excel explanation. Status is an event which shows when and event is occuring en when its gone, so in this situation 'gegaan' indicates when the event is dissapearing en ''gekomen'' is when an event starts. The ID's is an type of event. I want to know how long an event is occuring. Sidenote is that every Event is can happen multiple times and the events are occuring trough one and another. I need an Formula which calculates the time (gegaan-gekomen) per event. So i can see per ID how long the did occur.  

When my expected result still isn't clear, dont hesitate to ask 🙂

Best regards,

Wesley 

[Anonymous]

Hi @WmUit ,

I updated the formula of measure Duration as below, but it will get wrong result when there are multiple status(for example: id 210).... Could you please provide the calculation logic about this scenario?

Duration = 
var _selid=SELECTEDVALUE('Data'[ID])
VAR _gekindex =
    CALCULATE (
        MIN( 'Data'[Index] ),
        FILTER (
            ALLSELECTED( 'Data' ),
            'Data'[ID] = _selid
                && 'Data'[Status] = "Gegaan"
        )
    )
VAR _gegaindex =
    CALCULATE (
        MAX( 'Data'[Index] ),
        FILTER (
            ALLSELECTED ( 'Data' ),
            'Data'[ID] = _selid
                && 'Data'[Status] = "Gekomen"
                && SELECTEDVALUE('Data'[Index]) >=_gekindex
        )
    )
var _gegatime=CALCULATE(max('Data'[Date and Time]),FILTER(ALLSELECTED( 'Data' ),'Data'[ID]=_selid&&'Data'[Index]=_gegaindex))
RETURN
 DATEDIFF (  if(SELECTEDVALUE('Data'[Status] )= "Gegaan",_gegatime,BLANK()),SELECTEDVALUE('Data'[Date and Time]),  MINUTE )

Best Regards

[WmUit]

Hello @Anonymous many thanks for you reaction,

I tried your formula but i didn't get any results, Picture is below. For the index number I used a formula which was posted in another discussion. The Status can happen multiple times in a short period, its an indication from an event to start, so ''Gekomen'' means an event started and ''Gegaan'' means the event has ended, so the logic calculation is The end (gegaan) minus the beginning of the event (gekomen). So ''gegaan'' and ''Gekomen'' are the same as Start and End, like in the given picture you send with ID; 210, the first event happened from 11:58-12:01 and the second event (same ID) happened from 12:04-12:05.

Gekomen=Start Event

Gegaan=End Event

I tried your formula but didn't get any calculations, did I do something wrong, shown in the picture. 

[Anonymous · Accepted solution]

Hi @WmUit ,

In fact, what I created before is a measure not a calculated column... I created  a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Create a calculated column to get rank for per ID

Index = 
RANKX (
    FILTER (
        ALL ( 'Data' ),
        'Data'[Status]
            IN { "Gegaan", "Gekomen" }
                && 'Data'[ID] = EARLIER ( 'Data'[ID] )
    ),
    'Data'[Date and Time],
    ,
    ASC,
    DENSE
)

2. Create a measure or calculated column to get the duration

Measure:

Measure Duration = 
VAR _gektime =
    CALCULATE (
        MAX ( 'Data'[Date and Time] ),
        FILTER (
            ALLSELECTED ( 'Data' ),
            'Data'[ID] = SELECTEDVALUE ( 'Data'[ID] )
                && 'Data'[Status] = "Gekomen"
                && 'Data'[Index] <= SELECTEDVALUE ( 'Data'[Index] )
        )
    )
VAR _duration =
    DATEDIFF (
        IF ( SELECTEDVALUE ( 'Data'[Status] ) = "Gegaan", _gektime, BLANK () ),
        SELECTEDVALUE ( 'Data'[Date and Time] ),
        SECOND
    )
VAR _hours =
    INT ( _duration / 3600 )
VAR _minutes =
    INT ( MOD ( _duration - ( _hours * 3600 ), 3600 ) / 60 )
VAR _seconds =
    ROUNDUP ( MOD ( MOD ( _duration - ( _hours * 3600 ), 3600 ), 60 ), 0 )
VAR _hh =
    IF (
        LEN ( _hours ) = 1,
        CONCATENATE ( "0", _hours ),
        CONCATENATE ( "", _hours )
    )
VAR _mm =
    IF (
        LEN ( _minutes ) = 1,
        CONCATENATE ( "0", _minutes ),
        CONCATENATE ( "", _minutes )
    )
VAR _ss =
    IF (
        LEN ( _seconds ) = 1,
        CONCATENATE ( "0", _seconds ),
        CONCATENATE ( "", _seconds )
    )
RETURN
    IF (
        ISBLANK ( _duration ),
        BLANK (),
        CONCATENATE (
            _hh,
            CONCATENATE ( ":", CONCATENATE ( _mm, CONCATENATE ( ":", _ss ) ) )
        )
    )

Or  calculated column:

Calculated Column Duration = 
VAR _gektime =
    CALCULATE (
        MAX ( 'Data'[Date and Time] ),
        FILTER (
            ALLSELECTED ( 'Data' ),
            'Data'[ID] = EARLIER ( 'Data'[ID] )
                && 'Data'[Status] = "Gekomen"
                && 'Data'[Index] <= EARLIER ( 'Data'[Index] )
        )
    )
VAR _duration =
    DATEDIFF (
        IF ( 'Data'[Status] = "Gegaan", _gektime, BLANK () ),
        'Data'[Date and Time],
        SECOND
    )
VAR _hours =
    INT ( _duration / 3600 )
VAR _minutes =
    INT ( MOD ( _duration - ( _hours * 3600 ), 3600 ) / 60 )
VAR _seconds =
    ROUNDUP ( MOD ( MOD ( _duration - ( _hours * 3600 ), 3600 ), 60 ), 0 )
VAR _hh =
    IF (
        LEN ( _hours ) = 1,
        CONCATENATE ( "0", _hours ),
        CONCATENATE ( "", _hours )
    )
VAR _mm =
    IF (
        LEN ( _minutes ) = 1,
        CONCATENATE ( "0", _minutes ),
        CONCATENATE ( "", _minutes )
    )
VAR _ss =
    IF (
        LEN ( _seconds ) = 1,
        CONCATENATE ( "0", _seconds ),
        CONCATENATE ( "", _seconds )
    )
RETURN
    IF (
        ISBLANK ( _duration ),
        BLANK (),
        CONCATENATE (
            _hh,
            CONCATENATE ( ":", CONCATENATE ( _mm, CONCATENATE ( ":", _ss ) ) )
        )
    )

Best Regards

[WmUit]

Hello @Anonymous, Many thanks for you your time and solution, it is working perfectly. I did choose the calculated column for easier overview and checking times. Thanks again for the time and effort. 

Best Regards

Wesley