Forum Discussion
calculate time duration from the same column
Hello, I have a table with four columns: 'id', 'bay_id','dosage' and 'chemical' as below:
My question is that how can I calculate time duration when dosage is greater than 0 on a specific chemical status with the same bay_id. For example, for bay_id = 3528, Alkaline has been used twice, from '15/05/2020 17:01:39' to '15/05/2020 17:03:11'(duration is 01:32) and from '15/05/2020 17:03:53' to '15/05/2020 17:05:24' (duration is 01:31) so the total duration is 03:03.
2 Replies
- amitchandak
Super User
Anonymous , Create two columns like
Min time =maxx(FILTER('Table',[ID]=EARLIER([ID]) && [bay_id]=EARLIER([bay_id]) && [time_chemi]<EARLIER([time_chemi]) && [dosage]<>EARLIER([dosage])),[time_chemi])
max Time=minx(FILTER('Table',[ID]=EARLIER([ID]) && [bay_id]=EARLIER([bay_id]) && [time_chemi]>EARLIER([time_chemi]) && [dosage]<>EARLIER([dosage])),[time_chemi])
and then a measure like this
duration =
sumx(summarize(Table,table[ID], table[bay_id], Table[Min time],Table[max Time]),datediff([Min time],[max Time],minute))
