cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Calculate unique viewership duration

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 IDCustomer IDDateStart Index (secs)Stop Index (secs)Video Duration (secs)Video Name
112345620-Aug-204080240Short Movie 2
212345618-Aug-207090240Short 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.

13 REPLIES 13
Community Support
Community Support

Hi @wafster 

Create a date table

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

Capture10.JPG

 

Add column in Table5,

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

Measures

Capture9.JPG

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.

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 IDDateMovieStartIndexStopIndex
123456aug 20short movie32040
123456

aug 20

short movie33050
123456aug 20short movie36080
123456aug 20short movie390100

 

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?

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.

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

Provide some more sample data.

Super User III
Super User III

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.

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

Super User IV
Super User IV

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

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors