Calculate the time an object is active within time interval selected

[gedal]

Hi
I'm looking to calculate the time that a given object is active within a chosen timespan on the date dimension. I've tried to illustrate this in the picture here:

picture example: in this example the measure should return, that the object has been active for 15 days. 

data:

object id	status	start time	end time
1	active	25/03/2023	05/04/2023
1	down	05/04/2023	15/04/2023
1	active	15/04/2023	25/04/2023
1	down	25/04/2023	05/05/2023

Result wanted:

object id	active days
1	15

data model:

model explanation: the calendar dimension is connected to the fact with 2 inactive connecters. one to the start date and one to the end date. 

First i tried just calculating the time difference on between the dates in a new column on the fact, and wrote this calculation:

CALCULATE(

    SUM(FACT[Duration (ms)]),

    KEEPFILTERS(FACT[Status] = 'active'),

    FILTER('FACT',

        'FACT'[Date Start] >= MIN(calendar[Date])

        && 'FACT'[Date End]  <= MAX(calendar[Date])

    )

but this will not give the correct output. since it returns 0 if the start or end dates are not within the calendar selection. Also it will return the full amount of time (10 days) for the first active event in the picture above and not 5 days as needed. 

Does anyone have an idea of how to handle this request?

[lbendlin]

Your End Time and Start Time values overlap. That will not work / lead to duplication. You need better granularity.

[gedal]

thanks for reply 🙂
I'm not sure i see the same issue. Because i filter on status = active, so they will never overlap. Is there something in the background of PBI that I'm not aware of?
also what i'm trying to achive is basically an "event in progress calculation" but instead of counting events i want to sum the time. 

[lbendlin]

On 5/4/2023 was your Object 1 active or was it down?

[gedal]

the object is active, then down, then active then down. 
So  I wanna see: for how long was my object active within the timespan chosen on the calendar filter. The timespan selected is 30 days, and the object was active for 15 of those days. 

[lbendlin]

It was also down for 17 days in the same period. 15 + 17 = 30 ??