Forum Discussion

Anonymous

Not applicable

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

v-kelly-msft

5 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!

Help for calculating overlapping periods.pbix82 KB

Anonymous

Not applicable

5 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

Community Champion

5 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

Not applicable

5 years ago

Ty CNENFRNL!

But i would need a DAX solution for this case.

v-kelly-msft

Community Support

5 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!

Help for calculating overlapping periods.pbix82 KB

Anonymous
Not applicable
5 years ago
Thank you v-kelly-msft! Your code really helped me a lot.

I just made small adjustments to better fit my data.

Forum Discussion

Help for calculating overlapping periods

Related content

Calculate Overlap Period

Dynamic Calculation Different Period

2 visuals overlapping

Calculating % Overlap

Calculate period over period