topic Re: How to get downtime per day from multiple dates? in DAX Commands and Tips

How to get downtime per day from multiple dates?

Anonymous — Tue, 18 Jun 2024 08:16:13 GMT

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.

Re: How to get downtime per day from multiple dates?

lbendlin — Tue, 18 Jun 2024 23:57:48 GMT

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-Forum/ba-p/963216

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/1447523

Re: How to get downtime per day from multiple dates?

hackcrr — Tue, 02 Jul 2024 23:17:25 GMT

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