Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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/1718981#M679611  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!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous ,

        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