Find date with longest total duration sum (plus how to display decimal hours as hh:mm:ss)

[TomJWhite]

Hi there,

I am using a dataset of netflix viewing activity which has a column for [Start Time] e.g '12/30/2020 11:47:48 AM' and a column for [Duration] eg '00:14:58' (hh:mm:ss). Each entry is a viewing, with multiple viewings per day usually. Example below.

Title	Duration	Start Time	Total Hours
The Big Bang Theory: Season 6: The Date Night Variable (Episode 1)	00:14:58	12/30/2020 11:47:48 AM	0.25
Bridgerton: Season 1: Diamond of the First Water (Episode 1)	00:54:58	12/31/2020 1:30:34 PM	0.92
Bridgerton: Season 1: Shock and Delight (Episode 2)	00:22:16	12/31/2020 2:29:20 PM	0.37

I want to add a card visual showing 'Biggest Viewing Day' which would show the specific date with the highest total sum of [Duration] for entries on that day. Ideally something like "1 Hours 17 Minutes 14 Seconds - January 1st 2020"

I have already made another custom column for [Total Hours] which converts a given [Duration] entry to '0.25' etc which seems to work for making column charts for Total Hours by Year etc but I dont know how to go about calculating the sum of duration for each date in the dataset. I also ideally want to have cards for 'Total Hours' but that currently shows like '3,709.03' instead of in hours, minutes, seconds. 

Apologies if this is confusing, let me know if I need to clarify anything. 

[DataInsights · Accepted solution]

@TomJWhite,

Try this solution. It displays the result in hours, but you can format that as "hh:mm:ss" with the help of the article below.

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486 

1. Create a calculated column for Start Date. You can do this in Power Query or DAX. Here's the DAX:

Start Date = INT ( Table1[Start Time] )

2. Create measures:

Sum Total Hours = SUM ( Table1[Total Hours] )

Biggest Viewing Day =
VAR vBaseTable =
    ADDCOLUMNS ( VALUES ( Table1[Start Date] ), "@Hours", [Sum Total Hours] )
VAR vRankTable =
    ADDCOLUMNS ( vBaseTable, "@Rank", RANKX ( vBaseTable, [@Hours],, DESC, DENSE ) )
VAR vTopRank =
    FILTER ( vRankTable, [@Rank] = 1 )
VAR vTopHours =
    MAXX ( vTopRank, [@Hours] )
VAR vTopDate =
    MAXX ( vTopRank, Table1[Start Date] )
VAR vResult =
    vTopHours & " - "
        & FORMAT ( vTopDate, "mmmm d, yyyy" )
RETURN
    vResult

3. Result:

[v-yangliu-msft · Accepted solution]

Hi  @TomJWhite ,

Here are the steps you can follow：

1. Create calculated column.

Date =
DATE(YEAR('Table'[Start Time]),MONTH('Table'[Start Time]),DAY('Table'[Start Time]))

Duration_add =
SUMX(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])),[Duration])

2. Create measure.

Measure =
var _max=
MAXX(FILTER(ALL('Table'),'Table'[Duration_add]=MAX('Table'[Duration_add])),[Duration_add])
var _maxdate=
MAXX(FILTER(ALLSELECTED('Table'),'Table'[Duration_add]=_max),[Date])
return
HOUR(_max)&" Hours "&MINUTE(_max)&" Minutes "&SECOND(_max)&" Seconds - "&FORMAT(_maxdate,"mmmm")&" "&DAY(_maxdate)&"st "&YEAR(_maxdate)

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[DataInsights · Accepted solution]

@TomJWhite,

Try this solution. It displays the result in hours, but you can format that as "hh:mm:ss" with the help of the article below.

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486 

1. Create a calculated column for Start Date. You can do this in Power Query or DAX. Here's the DAX:

Start Date = INT ( Table1[Start Time] )

2. Create measures:

Sum Total Hours = SUM ( Table1[Total Hours] )

Biggest Viewing Day =
VAR vBaseTable =
    ADDCOLUMNS ( VALUES ( Table1[Start Date] ), "@Hours", [Sum Total Hours] )
VAR vRankTable =
    ADDCOLUMNS ( vBaseTable, "@Rank", RANKX ( vBaseTable, [@Hours],, DESC, DENSE ) )
VAR vTopRank =
    FILTER ( vRankTable, [@Rank] = 1 )
VAR vTopHours =
    MAXX ( vTopRank, [@Hours] )
VAR vTopDate =
    MAXX ( vTopRank, Table1[Start Date] )
VAR vResult =
    vTopHours & " - "
        & FORMAT ( vTopDate, "mmmm d, yyyy" )
RETURN
    vResult

3. Result: