How to get downtime per day from multiple dates?

[Anonymous]

Hello,

I want to create a calendar with the BciCalendar visual, showing each day's downtime. The thing is, I have multiple events with downtime that can exceed 1440 minutes (24 hours). I found the measure I need from another answer:
(How to get downtime per day when there are multiple days between start and end date?)

Duration Time 2 = 
SUMX ( 
    VALUES ( 'Date'[Date] ),
    VAR CurrentStartTime = 'Date'[Date]
    VAR CurrentEndTime = CurrentStartTime + TIME ( 23, 59, 59 )
    VAR FilteredTable = FILTER ( 'Table', CurrentStartTime IN CALENDAR ( 'Table'[Start].[Date], 'Table'[End].[Date] ) )
    RETURN
        SUMX (
            FilteredTable,
            DATEDIFF ( MAX ( CurrentStartTime, 'Table'[Start] ), MIN ( CurrentEndTime, 'Table'[End] ), SECOND )
        )
)

But now, when I use the measure, it reduces the downtime only for the first day when the event starts (just first 1440 minutes). For the rest of the days, the downtime stops being counted.

How can I make it show the downtime from the event for each day until it has been completed?

Thanks for support! I'm a powerBI beginner.

[hackcrr]

Hi, @Anonymous 

You can try :

Duration Time 2 = 
SUMX(
    VALUES('Date'[Date]),
    VAR CurrentDay = 'Date'[Date]
    VAR DayStart = CurrentDay
    VAR DayEnd = DayStart + TIME(23, 59, 59)
    VAR OverlappingEvents = 
        FILTER(
            'Table',
            'Table'[Start] <= DayEnd &&
            'Table'[End] >= DayStart
        )
    VAR TotalDowntimeInSeconds =
        SUMX(
            OverlappingEvents,
            DATEDIFF(
                MAX(DayStart, 'Table'[Start]),
                MIN(DayEnd, 'Table'[End]),
                SECOND
            )
        )
    RETURN
        TotalDowntimeInSeconds / 60 -- Convert seconds to minutes
)

Once you have created this measure, you can use it in your BciCalendar visual to show the downtime for each day, correctly accounting for downtimes that span multiple days.

hackcrr

If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly

[lbendlin]

Use a more appropriate visual.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...