Forum Discussion

BICrazy's avatar
BICrazy
Helper II
7 years ago
Solved

Drop Seconds from Duration Measure

Hi,

 

I'm very new to this forum and still a basic learner of Power BI.  I'm using the following 2 measures to display Duration as Hours minutes and seconds.

 

1st Measure:

Duration2Seconds =

VAR sub = SUBSTITUTE(MAX('TrackerDailySummary'[Duration]),":","|")

VAR hours = VALUE(PATHITEM(sub,1))*3600
VAR minutes = VALUE(PATHITEM(sub,2))*60
VAR seconds = VALUE(PATHITEM(sub,3))*1

RETURN hours+minutes+seconds

 

2nd Measure:

Convert to Seconds =
VAR Duration = SUMX(TrackerDailySummary,[Duration2Seconds])
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,"" )) )))

 

These are the results I'm returning, which is accurate.  

 

I'd like to drop off the seconds and just display the hours and minutes only.  Any suggestions on how I can adjust this code?

 

Regards

 

9 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion

    BICrazy

     

    you should just return your variables without the ts

     

    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 )))

    • BICrazy's avatar
      BICrazy
      Helper II

      Hi Vanessafvg,

       

      I tried not returning the seconds and the code doesn't work after that.  I think the entire needs to accommodate dropping the seconds.

      • vanessafvg's avatar
        vanessafvg
        Community Champion

        BICrazy what error are you getting?  you can return what you want, setting the variable doesn't mean you have to return it