Correct total amount of time for date ranges that can potentially overlap (even if partially)

[Cro-Magnon]

The table I have has the date range for which an equipment has been installed on a certain location.

Let's assume just 2 equipment and one location, and let's ignore months and days, for simplicity:

EQ_ID	LOC_ID	DATE_FROM	DATE_TO
1	1	2000-01-01	2002-01-01
2	1	2005-01-01	2010-01-01
1	1	2008-01-01	2012-01-01

I'm trying to determine for each location the total amount of time it has had equipment installed.
The problem is that multiple equipment can be installed at a certain location at any given time, but shall not be counted multiple times.

In the example above, Location 1 has had equipment installed a total of 9 years: 2 years from 2000 to 2002, and then 7 years from 2005 to 2012 (since the date ranges 2005 to 2010, and 2008 to 2012 partially overlap).

What is the best way to accomplish this in Power BI?

[johnt75]

You can create a measure to return the total number of days like

Total days installed =
VAR InstalledDates =
    SELECTCOLUMNS (
        GENERATE ( 'Table', CALENDAR ( 'Table'[Date_from], 'Table'[Date_to] ) ),
        [Date]
    )
VAR Result =
    COUNTROWS ( DISTINCT ( InstalledDates ) )
RETURN
    Result

[Deku · Accepted solution]

Added a date table to help with the calculation

dates = 
CALENDAR( min( 'Table'[DATE_FROM] ) , MAX( 'Table'[DATE_TO] ) )

daysPerLoc = 
var days = 
	GENERATE(
	VALUES( 'Table' )
	,DATESBETWEEN( dates[Date], 'Table'[DATE_FROM], 'Table'[DATE_TO] )
	) 
var distinctDayPerLocation =
	COUNTROWS( SUMMARIZE( days, 'Table'[LOC_ID], dates[Date] ) )
RETURN distinctDayPerLocation

yearsPerLocation = [daysPerLoc] / 365

[bhanu_gautam]

@Cro-Magnon 

Create a new table to hold the unique date ranges for each location. You can do this by creating a calculated table in Power BI.

UniqueDateRanges =
VAR DateRanges =
ADDCOLUMNS(
EquipmentInstallations,
"DateFrom", [DATE_FROM],
"DateTo", [DATE_TO]
)
VAR ExpandedRanges =
GENERATE(
DateRanges,
SELECTCOLUMNS(
CALENDAR([DateFrom], [DateTo]),
"Date", [Date]
)
)
RETURN
DISTINCT(ExpandedRanges)