Help for calculating overlapping periods

[Gbdate]

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

[Gbdate]

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]

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

[Gbdate]

Ty CNENFRNL!

But i would need a DAX solution for this case. 

[v-kelly-msft · Accepted solution]

Hi  @Gbdate ,

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!

[Gbdate]

Thank you v-kelly-msft! Your code really helped me a lot.

I just made small adjustments to better fit my data.  

[v-kelly-msft]

Hi  @Gbdate ,

Glad to help.

Best Regards,
Kelly

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

[Gbdate]

Guys, I need one more help from you. I'm stuck in the following situation: I need to create the following columns calculated in DAX, can you help me one more time? 🙂 

Dataset and calculated columns.

 https://docs.google.com/spreadsheets/d/1MuBf0FjDIUiPqBA3A4oU-DJFY-oD-KEX/edit?usp=sharing&ouid=11793... 

Failures Dataset2

[v-kelly-msft]

Hi  @Gbdate ,

Glad to help.

But would you pls create a new thread for us to follow?

Best Regards,
Kelly

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

[Gbdate]

For sure! I will be very grateful if you can help me one more time :). Below is the link to the new post:

https://community.powerbi.com/t5/Desktop/Help-for-creating-calculated-columns-adjustments-for-overla...