Forum Discussion

Eesa's avatar
Eesa
Frequent Visitor
4 years ago
Solved

Calculate Dynamically Change Duration

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 crea...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
amitchandak
Super User
4 years ago

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's avatar
    Eesa
    Frequent Visitor
    4 years ago

    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."