Forum Discussion
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
- Anonymous4 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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandakSuper User
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) ))- EesaFrequent 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."
- EesaFrequent 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]) + 1var 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- AnonymousNot 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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.