Calculate Dynamically Change Duration

[Eesa]

Hi, 

I have a FLIGHT table below 

I want to use a slicer in my dashbord and when I adjust the slicer between any date/time, I want to get the total time in air. 

To imagine this I created a simple illustration below. The red dashed line is my slicer to pick a start date/time and end date/time. Each blue line line represent a flight and its actual duration. 

Now if I want to adjust my slicer between 1/1/2021 19:00 and 1/2/2021 5:00, I must get a total time in air of 25 hrs. 
I have a calendar table that has a relation with FLIGHT table and in which i will adjust my slicer from. 

So, how to do that in Power BI? 

Thank you for your help

[Eesa]

I have re-written the measure to correct the error and works very well in matrix visual but it can't work with card visual as it gives me BLANK. 
Here is the measure: 

Time in Air =

var sdate = MINX(ALLSELECTED(CalendarDIM), CalendarDIM[Date])

var endate = MAXX(ALLSELECTED(CalendarDIM), CalendarDIM[Date]) + 1

var StartDate = MAX(MAX(Table[take off]), sdate)

var EndDate = MIN(MIN(Table[land on]), endate)

return CALCULATE( sumx(Table, DATEDIFF(StartDate, EndDate,MINUTE)) , FILTER(Table,

(Table[take off] >= StartDate && Table[take off] <= EndDate ) || (Table[land on] >= StartDate && Table[land on] <= EndDate )

Here is the result: 

I'll really appreciate if anyone can help me fix this. 
Thank you 

[Anonymous · Accepted solution]

Hi @Eesa ,

Try to create a new measure based on your origianl [Time in Air] measure.

Correct Measure with total =
SUMX ( 'Table', [Time in Air] )

Result is as below.

Best Regards,
Rico Zhou

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

[amitchandak]

@Eesa , Assume you have independent date and time slicer (not joined with table)

measure =
var _sdate = minx(allselected('Date'), 'Date'[Date])
var _edate = Maxx(allselected('Date'), 'Date'[Date])
var _stime = minx(allselected('Time'), 'Date'[Time])
var _etime = Maxx(allselected('Time'), 'Date'[Time])
var _st = _sdate + _stime
var _ed = _edate + _etime
var _start = max(Table[take off], _st)
var _end = min(Table[Land on], _ed)
return
calculate(sumx(Table, datediff(_start,_end,second)), filter( Table, (Table[take off] >= _st && Table[take off] <=ed) || (Table[Land on] >= _st && Table[Land on] <=ed) ))

[Eesa]

Thank you @amitchandak for your reply. 

I got an error message with max(Table[take off], _st). The message: "A single value for column 'take off' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."