Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help for calculating overlapping periods

Guys, I have a relatively complicated task, I need to calculate some maintenance indicators (Downtime and Availability). The problem is that there are overlapping failures, where I can't perform these calculations line by line. 

 

I would like to establish a dax where it is possible to calculate the CORRECT availability of the equipment, even when there are overlapping failures.

 

Dataset

Dataset

Added calculated columns

Add Calculated Columns KPI's

Where:

Worktime = Today - Start_work

Downtime = End_failure - Start_failure

Availability = 1-(downtime/worktime)

 

Here I perform the creation of two measures to calculate the availability KPI. But since I'm simply calculating line by line and WITHOUT disregarding overlaps, my final results are wrong.

KPIS's final calculation

 

Failure and overlap scheme

Overlapping failures and downtimes

  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi  Anonymous ,

     

    First create an index column;

    Then create 3 columns below:

    Mark =
VAR _previousend =
    CALCULATE (
        MAX ( 'Table'[End_failure] ),
        FILTER (
            'Table',
            'Table'[Equipament] = EARLIER ( 'Table'[Equipament] )
                && 'Table'[Index]
                    = EARLIER ( 'Table'[Index] ) - 1
        )
    )
RETURN
    IF ( 'Table'[Start_failure] < _previousend, 1, 0 )

    Downtime =
VAR _minindex =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        FILTER ( 'Table', 'Table'[Equipament] = EARLIER ( 'Table'[Equipament] ) )
    )
VAR _nextmark1 =
    CALCULATE (
        MAX ( 'Table'[Mark] ),
        FILTER (
            'Table',
            'Table'[Index] = _minindex + 1
                && 'Table'[Equipament] = EARLIER ( 'Table'[Equipament] )
        )
    )
VAR _index1 =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER (
            'Table',
            'Table'[Equipament] = EARLIER ( 'Table'[Equipament] )
                && 'Table'[Index] < EARLIER ( 'Table'[Index] )
                && 'Table'[Mark] = 0
        )
    )
VAR _start =
    CALCULATE (
        MAX ( 'Table'[Start_failure] ),
        FILTER (
            'Table',
            'Table'[Index] = _index1
                && 'Table'[Equipament] = EARLIER ( 'Table'[Equipament] )
        )
    )
VAR _nextmark2 =
    CALCULATE (
        MAX ( 'Table'[Mark] ),
        FILTER (
            'Table',
            'Table'[Equipament] = EARLIER ( 'Table'[Equipament] )
                && 'Table'[Index]
                    = EARLIER ( 'Table'[Index] ) + 1
        )
    )
RETURN
    IF (
        'Table'[Mark] = 0,
        IF (
            _nextmark1 = 0,
            DATEDIFF ( 'Table'[Start_failure], 'Table'[End_failure], DAY ),
            IF ( _nextmark1 = 1, 0 )
        ),
        IF (
            'Table'[Mark] = 1,
            IF (
                _nextmark2 = 0,
                DATEDIFF ( _start, 'Table'[End_failure], DAY ),
                IF ( _nextmark2 = 1, 0 )
            )
        )
    )

    Availability =
VAR _Worktime =
    DATEDIFF ( 'Table'[Start_work], TODAY (), DAY )
VAR _downtime =
    SUMX (
        FILTER ( 'Table', 'Table'[Equipament] = EARLIER ( 'Table'[Equipament] ) ),
        'Table'[Downtime]
    )
RETURN
    1 - DIVIDE ( _downtime, _Worktime )

     

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I thought of solving it as follows:

     

    1 - Add three calculated columns (Internal_Failure_ID, Start_failure_acumulated, End_failure_acumulated). Where the "acumulated columns" would calculate the conditions of intersection between failures of the same equipment, thus generating the "Internal_ID_Failure".

     

    2 - It would only consider faults with "Internal_ID_Failure" distinct for the final calculation of the availability KPI, calculating its unavailability using the following formula:

     

    Availability = 1 -Unavailability (days)

     

    and

     

    Unavailability (days) = Downtime (days) / Worktime (days)

     

    But I can't run the dax of this scheme.

     

    Do you have any idea how to do this in dax?

     

     

                

     

     

     

    • CNENFRNL's avatar
      CNENFRNL
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Fairly easy to combine overlapping lists in PQ,

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY5LCsAgDAXv4lrIR6112QuU7sX7X6PRWFr7AxcDb4iTs1kXdMYaAgJGmk9MDYMgkyn2zZza3E3yf6og1h2FE3C8uFuKVUCV78zg9MaXC+yPT4L2fLsM8nqxG4uftpi6D8VlBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Equipment = _t, Start_work = _t, Start_failure = _t, End_failure = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Equipment", type text}, {"Start_work", type date}, {"Start_failure", type date}, {"End_failure", type date}}),

    #"Combined Failures" = Table.Group(#"Changed Type", {"Equipment","Start_work"}, {{"Failure", each List.Combine(List.Transform(Table.ToRecords(_), each {Number.From([Start_failure])..Number.From([End_failure])}))}}),
    Availability = Table.AddColumn(#"Combined Failures", "Availability", each let dates = {Number.From([Start_work])..Number.From(Date.From(DateTime.LocalNow()))}, avail = List.Difference(dates, [Failure]) in List.Count(avail) / List.Count(dates), Percentage.Type)
in
    Availability

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Ty