Measure on date based on start and end time

[user023]

Hi, I have data like this. 

I would like to have a between date slicer and from the min and max date to take total actual running time. 

Also, I would like it to consider the workcenter number (will have it as filter also)

 

ReportedDate	StartTime	EndTime	OrderNumber	WorkCenterNumber	ActualRunningTime (Hour)
11-04-2023	11-04-2023 23:26	12-04-2023 0:01	Break time	0101-101	0.583333333
11-04-2023	11-04-2023 23:26	11-04-2023 23:59	Break time	0101-101	0.56
12-04-2023	12-04-2023 8:01	12-04-2023 10:05	Other	0101-201	2.066666667
12-04-2023	12-04-2023 21:32	16-04-2023 21:32	No Schedule	0101-101	96
12-04-2023	12-04-2023 13:26	12-04-2023 14:05	Break time	0101-201	0.65

 

Example : 

If I filter date between 11-04-2023 until 11-04-2023 with work center 0101-101 

Result should be like below.  

ReportedDate	StartTime	EndTime	OrderNumber	WorkCenterNumber	ActualRunningTime (Hour)
11-04-2023	11-04-2023 23:26	11-04-2023 23:59	Break time	0101-101	0.56

 

If I filter date between 12-04-2023 until 13-04-2023 with work center 0101-101 & 0101-201

Result should be like below. 

ReportedDate	StartTime	EndTime	OrderNumber	WorkCenterNumber	ActualRunningTime (Hour)
12-04-2023	12-04-2023 00:00	12-04-2023 0:100	Break time	0101-101	0.00001
12-04-2023	12-04-2023 8:01	12-04-2023 10:05	Other	0101-201	2.066666667
12-04-2023	12-04-2023 21:32	12-04-2023 11:59	No Schedule	0101-101	96
12-04-2023	12-04-2023 13:26	12-04-2023 14:05	Break time	0101-201	0.65

 

In short, I would like sum actual running hour that fall within my selected date

 

[user023]

Hi, 

I already try above example but sum the running time will getting wrong result example if I filter date 12/6/2023 my start date 10/6/2023 and end date 12/6/2023 will be listed. (actual running time will be 48hrs) but with my filter it should show only 24hour. 

[user023]

So I am thinking something like, do checking if there are cross day between starttime and endtime  automatic take max date to minus starttime (but result also might not very correct what if starttime 2days back) 

Duration III =

var maxdate = MAXX(ALLSELECTED('Calendar'), 'Calendar'[DateTime])

var mindate = MINX(ALLSELECTED('Calendar'), 'Calendar'[DateTime])

var datediff = DATEDIFF(mindate, maxdate, day)  

var time = if(datediff > 0 , maxdate - starttime, sum(Results[ActualRunningTime]) )

return CALCULATE( SUM(time),

filter ( Results, Results[EndTime] >= mindate

&&

Results[StartTime] <= maxdate ))

[amitchandak]

@user023 , Try a measure like with slicer on independent date table

//Date1 is independent Date table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[End Time] >=_min && 'Table'[Start Time Time] <=_max))