MCF Mean cumulative function in Power BI

[Anonymous]

I have been trying to get this to work for weeks and finally found the courage to ask my fellow Power BI hustlers for help.

The theory is simple: The MCF cumulates the number of events (e.g. repairs) over time. This leads to a "step function" like in this example: 

The size/height of a step is calculated: step size (t) = 1/number of active machines at time t

Now let's take this sample data

Maschine number	Repair ID	Time of failure (in days after maschine was marked as active)
1	1	35
1	2	51
1	3	69
1	4	70
1	5	78
2	6	34
2	7	55
2	8	55
2	9	74
2	10	96
2	11	339
2	12	453

Simply cumulating the events over time is easy:

Events cumulated =
CALCULATE (
    DISTINCTCOUNT ( data[repair_id] ),
    FILTER (
        ALL ( data[Time of failure] ),
        data[Time of failure] <= MAX ( data[Time of failure] )
    )
)

The number of active maschines:

No maschines =
CALCULATE (
    DISTINCTCOUNT ( data[Maschine number] ),
    data[Time of failure] >= MAX ( data[Time of failure] )
)

The problem is bringing the the measures together to get the right step sizes for the function. I have tried many variations of the following:

MCF =
CALCULATE (
    DISTINCTCOUNT ( data[repair_id] ),
    FILTER (
        ALL ( data[Time of failure] ),
        data[Time of failure] <= MAX ( data[Time of failure] )
    )
) / [No maschines]

While both maschines are active the step size is correct as 1 / 2 = 0,5. But after the functions goes crazy.

Please, does anyone know what I am doing wrong? Thank you so much!

[ZvikaBK2801]

Hi, 

Your solution is nice but cannot proprly handle a realistic situation in which the time to event include system censoring events as well as incomplete recurrent data with right censoring events. I have complete solution to both, as well as other realistic techenical issues. BTW, what about calculating confidence intervals around MCF? Let me know if you are interested.

[Anonymous · Accepted solution]

For anyone interersted, I believe to have found a solution to my problem.

No maschines =
CALCULATE (
    DISTINCTCOUNT ( data[Maschine number] ),
    data[Time of failure] >= MAX ( data[Time of failure] )
)

Step_Size = DISTINCTCOUNT(data[repair_id]) / [No machines]

MCF = 
SUMX(
	FILTER(
		ALLSELECTED(data[Time of failure]),
		data[Time of failure] <= MAX ( data[Time of failure] )
	),
    [Step_Size]
)

[lbendlin]

your data seems to be missing a timestamp column? How would you know both machines are active at the same time?

[Anonymous]

Hello @lbendlin, thank you for replying.

In my opinion, a timestamp is not needed (see also example here: MCF github . For simplicity I would assume that machines are not active anymore, after the last repair event (see my second measure "No machines") . I am adding timestamp columns to the data below, in case it can help. Thank you again for trying to help!

Maschine number	Maschine activation date	Repair ID	Repair date	Time of failure (in days after maschine was marked as active)
1	22.08.2022	1	26.09.2022	35
1	22.08.2022	2	12.10.2022	51
1	22.08.2022	3	30.10.2022	69
1	22.08.2022	4	31.10.2022	70
1	22.08.2022	5	08.11.2022	78
2	18.03.2022	6	21.04.2022	34
2	18.03.2022	7	12.05.2022	55
2	18.03.2022	8	12.05.2022	55
2	18.03.2022	9	31.05.2022	74
2	18.03.2022	10	22.06.2022	96
2	18.03.2022	11	20.02.2023	339
2	18.03.2022	12	14.06.2023	453