Calculating Error Duration between 2 dates for Machines

[Anonymous]

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..

[v-yingjl]

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.

[amitchandak]

@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]

Hi Amit ,

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

Swathi