Calculate unique viewership duration

You should be able to use a simple SUMX measure to get your result like this (replace "Data" with your actual table name)

Total Duration Viewed = SUMX(Data, Data[Stop Index (secs)] - Data[Start Index (secs)])

You can use the above in a table visual with Customer and/or Video Name (or just as a card visual).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

For your overlap periods you can either deploy an EXCEPT function, so that the duration would be 

(A EXCEPT B ) + B

but that would require you do decide on a granularity ( i guess you already settled on seconds) and then generate series on the fly.

The alternative would be to realize that Power BI is not the right tool for your requirement.

Hi Anonymous 

Create a date table

Date1 = ADDCOLUMNS(CALENDARAUTO(),"year-month",FORMAT([Date],"yyyy-mm"))

Add column in Table5,

month = FORMAT([Date],"yyyy-mm")

Measures

s_Min =
CALCULATE (
    MIN ( 'Table 5'[Start Index (secs)] ),
    FILTER (
        ALLSELECTED ( 'Table 5' ),
        'Table 5'[Customer ID] = MAX ( 'Table 5'[Customer ID] )
            && 'Table 5'[Video Name] = MAX ( 'Table 5'[Video Name] )
            && 'Table 5'[month] = MAX ( 'Table 5'[month] )
    )
)


s_Max =
CALCULATE (
    MAX ( 'Table 5'[Start Index (secs)] ),
    FILTER (
        ALLSELECTED ( 'Table 5' ),
        'Table 5'[Customer ID] = MAX ( 'Table 5'[Customer ID] )
            && 'Table 5'[Video Name] = MAX ( 'Table 5'[Video Name] )
            && 'Table 5'[month] = MAX ( 'Table 5'[month] )
    )
)


e_Min =
CALCULATE (
    MIN ( 'Table 5'[Stop Index (secs)] ),
    FILTER (
        ALLSELECTED ( 'Table 5' ),
        'Table 5'[Customer ID] = MAX ( 'Table 5'[Customer ID] )
            && 'Table 5'[Video Name] = MAX ( 'Table 5'[Video Name] )
            && 'Table 5'[month] = MAX ( 'Table 5'[month] )
    )
)


e_Max =
CALCULATE (
    MAX ( 'Table 5'[Stop Index (secs)] ),
    FILTER (
        ALLSELECTED ( 'Table 5' ),
        'Table 5'[Customer ID] = MAX ( 'Table 5'[Customer ID] )
            && 'Table 5'[Video Name] = MAX ( 'Table 5'[Video Name] )
            && 'Table 5'[month] = MAX ( 'Table 5'[month] )
    )
)

sec total = IF([s_Max]<=[e_Min],[e_Max]-[s_Min],[e_Min]-[s_Min]+[e_Max]-[s_Max])

duration_f =
IF (
    [sec total] <> BLANK (),
    IF (
        [s_Max] <= [e_Min],
        [s_Min] & "-" & [e_Max],
        [s_Min] & "-" & [e_Min] & "," & [s_Max] & "-" & [e_Max]
    )
)

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