Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Calculating Error Duration between 2 dates for Machines

Hi ,

I have a set of data of machines running in a factory as given below :

Error_Type     Downtime_Duration       Downtime_Started           Downtime_Ended           Machine       Val

Pow_Fail                 23                          5/2/2020 7:35:53 AM      5/2/2020 7:58:55 AM          Mn1            1

Pow_Fail                 28                          5/2/2020 8:00:06 AM      5/2/2020 8:28:55 AM          Mn1            1

Pow_Fail                 28                          5/2/2020 8:30:05 AM      5/2/2020 8:58:56 AM          Mn1            1

Pow_Fail                 23                          5/2/2020 9:00:06 AM      5/2/2020 9:28:56 AM          Mn1            2

 

Downtime Duration is calculated as difference between Downtime_Ended & Downtime_Started .

My requirement is , for each distinct machine & each distinct error type , consider downtime duration only when value in column "Val" has been incremented .

i.e) if Val = 1 for adjucent rows , no error has occured (Rows 2 & 3 need not be considered since no error has occured) , but when Val = 2 , then an error has occured (Duration from row 4 need to be considered ) .

 

Ultimately I need to find total time lost as downtime during a particular Operational Day.

 

Pls Help me .

 

Thanks in Advance..

 

 

 
 
General Comment
Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , try like

    diff last downtime end = datediff(maxx(filter(table, [Downtime_Started]<earlier([Downtime_Started]) && [machine]<earlier([machine])),[Downtime_Ended]),[Downtime_Ended],minute)

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Amit ,

       

      Tried your solution but I'm not getting any value . The output seems to be blank.

       

      Swathi

       
       
  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    Hi Anonymous ,

    You can try this measure if I got it correctly:

    Measure = 
VAR _startdate =
    SELECTEDVALUE ( 'Table'[Downtime_Started] )
VAR _val =
    SELECTEDVALUE ( 'Table'[Val] )
VAR _lastval =
    LOOKUPVALUE (
        'Table'[Val],
        'Table'[Downtime_Started], CALCULATE (
            MAX ( 'Table'[Downtime_Started] ),
            FILTER (
                ALLEXCEPT ( 'Table', 'Table'[Error_Type], 'Table'[Machine] ),
                'Table'[Downtime_Started] < _startdate
            )
        )
    )
VAR _re =
    CALCULATE (
        MAX ( 'Table'[Downtime_Duration] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Error_Type], 'Table'[Machine] ),
            'Table'[Val] = _val
                && 'Table'[Downtime_Started]
                    = CALCULATE (
                        MIN ( 'Table'[Downtime_Started] ),
                        FILTER (
                            ALLEXCEPT ( 'Table', 'Table'[Error_Type], 'Table'[Machine] ),
                            'Table'[Val] = _val
                        )
                    )
        )
    )
VAR _re2 =
    CALCULATE (
        MAX ( 'Table'[Downtime_Duration] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Error_Type], 'Table'[Machine] ),
            'Table'[Val] = _lastval
                && 'Table'[Downtime_Started]
                    = CALCULATE (
                        MIN ( 'Table'[Downtime_Started] ),
                        FILTER (
                            ALLEXCEPT ( 'Table', 'Table'[Error_Type], 'Table'[Machine] ),
                            'Table'[Val] = _lastval
                        )
                    )
        )
    )
RETURN
    IF (
        ISBLANK ( _lastval ),
        SELECTEDVALUE ( 'Table'[Downtime_Duration] ),
        IF ( _lastval = _val, _re, _re + _re2 )
    )

    By the way, when val =2, why duration is 23 not 28 if it calculates the datediff..

    Sample file is attached, please check and try it: Calculating Error Duration between 2 dates for Machines.pbix 

     

    Best Regards,
    Yingjie Li

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