Modelling Duration

[Anonymous]

Hi there,

Very new to PowerBI and I'm trying to model a column of data so that I may use the a SUM to total hours logged in a report.  I have been able to set the data type to Time, but not Duration and therfore SUM is not an option.

Any help appreciated.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

You could refer this thread to sum a "time" column.

https://community.powerbi.com/t5/Desktop/Sum-of-H-MM-SS/td-p/124007

For example, if you want to sum the "time" column based on each date, you can try the example below

SumTalkTime = 

VAR TotalSeconds=SUMX(FILTER('Table','Table'[date]=EARLIER('Table'[date])),HOUR('Table'[Column])*3600+MINUTE('Table'[Column])*60+SECOND('Table'[Column]))
VAR Days =TRUNC(TotalSeconds/3600/24)
VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)
VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60)
VAR Secs = MOD(TotalSeconds,60)
return IF(DAYS=0,"",IF(DAYS>1,DAYS&"days ",Days&"day"))&IF(Hors<10,"0"&Hors,Hors)&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)

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.

[ibarrau]

Hi, no worries. Go to edit queryes, pick the type Time column and the go to "Add Column" Tab. Check the right top corner you will find options for the hour/time. This will create a new column of the operation option you choose. Then you can sum that column.

Regards, hope this helps,

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