Forum Discussion

Ofire's avatar
Ofire
New Member
5 years ago
Solved

Display activities durations using Power BI

Hi, 
I am using power bi in a manufacturing environment. 
As part of it I would like to monitor the durations that some system was On and the duration it was Off. 
This system logging events of Start and Stop with the timestamp.  
I have this data in specific table (the fields are timestamp & event)
Duration between Start to Stop is the diff between the Stop Timestamp to the Start one and this is the duration system was On. 

Duration between Stop to next Start is the diff between the Start Timestamp to the Stop one and this is the duration system was Off. 
I also have Date & Time dimension table. 

 

Could someone please propose me how to display in a visual the durations that system was on and off ?  and also how to configure relationship in the model ?


Thank you 

 


Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Ofire ,

     

    Assumed that your data is the same structure as the sample below:

     

     

    Then you can use the following two measure:

     

    Onduration =
VAR Duration =
    SUMX (
        'Table',
        IF (
            'Table'[event] = "start",
            VAR a = 'Table'[Timestap]
            VAR b =
                CALCULATE (
                    MIN ( 'Table'[Timestap] ),
                    FILTER ( 'Table', 'Table'[Timestap] >= a && 'Table'[event] = "stop" )
                )
            RETURN
                DATEDIFF ( a, b, SECOND ),
            0
        )
    )
VAR Hours =
    INT ( Duration / 3600 )
VAR Minutes =
    INT ( MOD ( Duration, 3600 ) / 60 )
VAR Seconds =
    MOD ( MOD ( Duration, 3600 ), 60 )
VAR H =
    IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ) )
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
RETURN
    CONCATENATE (
        H,
        CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
    )

Offduration =
VAR Duration =
    SUMX (
        'Table',
        IF (
            'Table'[event] = "stop",
            VAR a = 'Table'[Timestap]
            VAR b =
                CALCULATE (
                    MIN ( 'Table'[Timestap] ),
                    FILTER ( 'Table', 'Table'[Timestap] >= a && 'Table'[event] = "start" )
                )
            RETURN
                DATEDIFF ( a, b, SECOND ),
            0
        )
    )
VAR Hours =
    INT ( Duration / 3600 )
VAR Minutes =
    INT ( MOD ( Duration, 3600 ) / 60 )
VAR Seconds =
    MOD ( MOD ( Duration, 3600 ), 60 )
VAR H =
    IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ) )
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
RETURN
    CONCATENATE (
        H,
        CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
    )

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

     

     

     

2 Replies

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Ofire ,

     

    Assumed that your data is the same structure as the sample below:

     

     

    Then you can use the following two measure:

     

    Onduration =
VAR Duration =
    SUMX (
        'Table',
        IF (
            'Table'[event] = "start",
            VAR a = 'Table'[Timestap]
            VAR b =
                CALCULATE (
                    MIN ( 'Table'[Timestap] ),
                    FILTER ( 'Table', 'Table'[Timestap] >= a && 'Table'[event] = "stop" )
                )
            RETURN
                DATEDIFF ( a, b, SECOND ),
            0
        )
    )
VAR Hours =
    INT ( Duration / 3600 )
VAR Minutes =
    INT ( MOD ( Duration, 3600 ) / 60 )
VAR Seconds =
    MOD ( MOD ( Duration, 3600 ), 60 )
VAR H =
    IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ) )
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
RETURN
    CONCATENATE (
        H,
        CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
    )

Offduration =
VAR Duration =
    SUMX (
        'Table',
        IF (
            'Table'[event] = "stop",
            VAR a = 'Table'[Timestap]
            VAR b =
                CALCULATE (
                    MIN ( 'Table'[Timestap] ),
                    FILTER ( 'Table', 'Table'[Timestap] >= a && 'Table'[event] = "start" )
                )
            RETURN
                DATEDIFF ( a, b, SECOND ),
            0
        )
    )
VAR Hours =
    INT ( Duration / 3600 )
VAR Minutes =
    INT ( MOD ( Duration, 3600 ) / 60 )
VAR Seconds =
    MOD ( MOD ( Duration, 3600 ), 60 )
VAR H =
    IF ( LEN ( Hours ) = 1, CONCATENATE ( "0", Hours ), CONCATENATE ( "", Hours ) )
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )
RETURN
    CONCATENATE (
        H,
        CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
    )

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai