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

Re: Sum of duration of task with Days, Hours, Minutes

Hi @brianhackett5

 

Yeah it can be - dependant on your scenario.

What type of data filed is your seconds in?
Also - are you looking for hh:mm:ss or hhh:mm:ss  (ie - 24 hour clock [max: 23:59:59] or something 26:12:00 [ which would be 26 hours and 12 minutes] if you see what I mean?)

 

That would change how you modulate the code

Highlighted
Helper I
Helper I

Re: Sum of duration of task with Days, Hours, Minutes

It is in an excel file formatted as a decimal number. I've then converted it to duration and then total seconds in the BI query editor. 

 

The report is made up of journey durations. The end goal is to display the monthly driving time in the format HH:MM:SS if possible?

Highlighted
Helper I
Helper I

Re: Sum of duration of task with Days, Hours, Minutes

Just reading your reply again. It would be hhhh:mm:ss. 

 

Total company driving would be approx 5000hrs a month

 

Highlighted
Helper I
Helper I

Re: Sum of duration of task with Days, Hours, Minutes

Hi @brianhackett5

Could you send me a screen shot example of the data (seconds) and i'll create a solution based on your example using your table names, etc.

Highlighted
Helper I
Helper I

Re: Sum of duration of task with Days, Hours, Minutes

Driving Seconds.JPGTot Driving Seconds.JPG

 

 

Highlighted
Helper I
Helper I

Re: Sum of duration of task with Days, Hours, Minutes

The second photo is is the total driving seconds for August and part of July. 

Highlighted
Helper I
Helper I

Re: Sum of duration of task with Days, Hours, Minutes

Hi @brianhackett5

 

I've built you a solution: Replace   (TestTable,TestTable[DRIVING TIME])   with your tablename and seconds field

 

Let me know if this works for you 🙂


Use this:

 

measure =
VAR Duration = SUMX(TestTable,TestTable[DRIVING TIME])
VAR Hours = INT(Duration/3600)
VAR Minutes = INT(MOD(Duration,3600)/60)
VAR Seconds = MOD(Duration,60)

VAR H =
IF ( LEN ( Hours ) = 1,
CONCATENATE ( "0", Hours ),
CONCATENATE ( "", Hours )
)
// Minutes with leading zeros
VAR M =
IF (
LEN ( Minutes ) = 1,
CONCATENATE ( "0", Minutes ),
CONCATENATE ( "", Minutes )
)
// Seconds with leading zeros
VAR S =
IF (
LEN ( Seconds ) = 1,
CONCATENATE ( "0", Seconds ),
CONCATENATE ( "", Seconds )
)


VAR tH = IF(Hours=0,"",IF(Hours>1,Hours&" Hours, ",Hours&" Hour, "))
VAR tM = IF(Minutes=0,"",IF(Minutes>1,Minutes&" Minutes, ",Minutes&" Minute, "))
VAR tS = IF(Seconds=0,"",IF(Seconds>1,Seconds&" Seconds",Seconds&" Second"))

RETURN
CONCATENATE (th, CONCATENATE ("",CONCATENATE (tm,CONCATENATE ("", CONCATENATE (ts,"" )) )))

 

Highlighted
Helper I
Helper I

Re: Sum of duration of task with Days, Hours, Minutes

Hi Acyrus1992,

 

This works really well.

 

One thing though, it wont change when using slicers for different teams/drivers etc?

Highlighted
Helper I
Helper I

Re: Sum of duration of task with Days, Hours, Minutes

Hi Acyrus1992,

 

I know why it wasnt working; I was doing it as a column instead of a measure.

 

It works perfectly. Thank you so much for your help, I really appreciate it.

 

Have a noice weekend 🙂

Highlighted
Helper I
Helper I

Re: Sum of duration of task with Days, Hours, Minutes

No problem

 

Have a great one yourself 🙂

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors