Calculate Overlap Period

Hi azeem 

You can certainly write a measure along these lines, and I would be interested to hear how it performs with your actual dataset.

You can use GENERATE with GENERATESERIES to create the required list of "second values" per row in a new column. Then select this column and count distinct values.

The measure would look something like this:

Total Hours = 
VAR SecondsPerHour = 3600
VAR SecondsPerDay = SecondsPerHour * 24
VAR SecondIndex =
    SELECTCOLUMNS (
        GENERATE (
            Data,
            GENERATESERIES (
                INT ( Data[Start Time] * SecondsPerDay ),
                INT ( Data[End Time] * SecondsPerDay )
            )
        ),
        "@SecondIndex", [Value]
    )
VAR NumSeconds =
    COUNTROWS (
        DISTINCT ( SecondIndex )
    )
VAR Hours = NumSeconds / SecondsPerHour
RETURN
    Hours

Alternatively:

Total Hours v2 = 
VAR SecondsPerHour = 3600
VAR SecondsPerDay = SecondsPerHour * 24
VAR SecondIndex =
    SUMMARIZE (
        GENERATE (
            Data,
            GENERATESERIES (
                INT ( Data[Start Time] * SecondsPerDay ),
                INT ( Data[End Time] * SecondsPerDay )
            )
        ),
        [Value]
    )
VAR NumSeconds =
    COUNTROWS ( SecondIndex )
VAR Hours = NumSeconds / SecondsPerHour
RETURN
    Hours

We multiply by SecondsPerDay in order to convert serial numbers (where an interval of a day corresponds to a value of 1) into seconds.

The values created by GENERATESERIES should be well within the Power BI's integer bounds for any conceivable date/time values.

One thing to note is that both the start and end times are included. So if Start Time and End Time are equal, the duration for that row would be one second. This could be tweaked if required.

Does this work for you?

Regards,

Owen