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

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

4 Replies

  • 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

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

  • Eesa's avatar
    Eesa
    Frequent Visitor

    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's avatar
      Anonymous
      Not applicable

      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.