Calculate Overlap Period

[azeem]

Hi, I have two columns represent the start time & end time for each activities. Each of the periods are overlapping. I would like to calculate total hours (considering the overlapping periods). The solution that I've found takes too long as I have 10k rows. Therefore, I would like to create a measure using GENERATESERIES for each row for each second and UNION them into a column to DISTINCTCOUNT them to get the total seconds then convert them to total hours (that I think will make the DAX code runs faster). How I could achieve that? Thanks

[OwenAuger · Accepted solution]

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