Calculate unique viewership duration

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.