Count duration over overlapping dates across multiple date range

[Anonymous]

Hi,

I’m very much new in power bi. it is my first post and need your help to get the correct duration. We open multiple job orders in our workshop for equipment at a time. I just want to calculate the correct duration from the table. Especially for the yellow highlighted equipment where the minimum start date is 3/26/2021 11:00:00 AM and the maximum end date is 4/14/2021 7:07:34 PM and both are on a different row.

Job Order No_	Equipment No_	Start Date	End Date	Duration(hours)	Remarks
5802	5841	3/26/2021 11:00:00 AM	04-12-2021 18:27	0	3/26/2021 11:00:00 AM minimum date
5760	5841	3/26/2021 1:41:24 PM	3/27/2021 4:00:00 PM	0
194	5841	3/26/2021 4:00:00 PM	4/14/2021 7:07:34 PM	464	4/14/2021 7:07:34 PM Maximum date
184	5841	3/30/2021 4:00:00 PM	4/14/2021 6:56:27 PM	0
334	5841	4/23/2021 10:07:34 AM	4/23/2021 1:00:00 PM	3
583	5841	05-10-2021 12:00	05-10-2021 13:00	1
642	5432	5/19/2021 10:25:51 AM	5/19/2021 12:00:00 PM	2

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

try to create a measure like this:

_Duration(hours) = 
var _table=FILTER(ALLEXCEPT('Table','Table'[Equipment No_]),DATEDIFF([Start Date],MAX([Start Date]),DAY)=0)
var _minS=CALCULATE(MIN([Start Date]),_table)
var _maxE=CALCULATE(MAX([End Date]),_table)
return DATEDIFF(_minS,_maxE,HOUR)

result:

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.