Calculate unique viewership duration

[Anonymous]

hi,

I have that captures viership of media/video content like netflix shown in table below. Row 1 captures viewership of customer "123456" watching video "Short Movie 2" that is 240 secs in duration on 20th Aug 2020, he/she started waching the video on the 40sec and stopped watching 40 secs later at index 80 secs.

Unique ID	Customer ID	Date	Start Index (secs)	Stop Index (secs)	Video Duration (secs)	Video Name
1	123456	20-Aug-20	40	80	240	Short Movie 2
2	123456	18-Aug-20	70	90	240	Short Movie 2

My objective is - at the end of each month, for each customer and each video, calculate the sum of unique viewership duration. i.e. as you can see in row 1 and 2 there is duration overlap between index 70 and 80 secs.

I have the data table above queried on power query, what do I do next to generate a monthly report on powerbi for this.

Appreciate everyone's help.

[v-juanli-msft]

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.

[Anonymous]

Hi @v-juanli-msft Maggie

I believe this is the closest to the final solution. Absolutely Brilliant. However, I believe, the formula fails for the following scenario

Customer ID	Date	Movie	StartIndex	StopIndex
123456	aug 20	short movie3	20	40
123456	aug 20	short movie3	30	50
123456	aug 20	short movie3	60	80
123456	aug 20	short movie3	90	100

in this case [sec_total] = 30. But actual unique duration for the month of august = 60. It misses out row 3 & 4.

Any thoughts on this?

[lbendlin]

I thought about it some more and here is what I would do: 

For each customer and video collect all the viewed seconds ( via GENERATESERIES ) into a table variable and then do a DISTINCTCOUNT on the second values. That has the same effect as EXCEPT but across all video fragments.

[Anonymous]

@lbendlini am very keen to try your proposed solutions, however I am having trouble understanding how to execute it

[lbendlin]

Provide some more sample data.

[Anonymous]

@lbendlin @mahoneypat @v-juanli-msft 

Hi sample data can be downloaded here

https://1drv.ms/u/s!Ai3SbGgc5UL94FbcuSVe9SAOyrFu?e=ZNMHKK

[lbendlin]

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.

[Anonymous]

@lbendlinwhat would be A and B in the formula you mentioned? And, what tool would you suggest instead of PowerBI

[mahoneypat]

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

[Anonymous]

@mahoneypat how do i ensure i dont include the overlap in duration?

[mahoneypat]

I thought you wanted to make sure overlapping views were included not excluded.  Here is a different measure that will calculate the overall duration watched for each customer and video combination.  Note this works with your example data, but if you have a customer that watched the first 10 sec and last 10 sec of a video, it would calculate the whole video duration.  A more complex expression would be needed to avoid that.  I didn't have time to do that now, but may later.  Perhaps the below approach will be enough, or get you started.

NewMeasure =
SUMX (
SUMMARIZE ( Table, Table[Customer ID], Table[Video Name] ),
CALCULATE ( MAX ( Table[Stop Index] ) - MIN ( Table[Start Index] ) )
)

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

Regards,

Pat

[mahoneypat]

Did you try the SUMX measure?  It calculates the difference on each row and then sums up the results, so should avoid overlapping values.

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

Regards,

Pat

[Anonymous]

@mahoneypathowever there will be overlap in duration between each row.