Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

convert seconds column into duration column mm:ss

 
I tried the following DAX query, but its not given as a expected output
 
 
Mins : Sec =

 

VAR Duration = sum(TABLE1[seconds])

 

VAR Minutes =
INT ( MOD( Duration,3600 ) / 60)

 

VAR Seconds =
ROUNDUP(MOD ( MOD( Duration,3600 ), 60 ),0) // We round up here to get a whole number

 

// 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 )
)
// Now return minutes and seconds with leading zeros in the proper format "mm:ss"
RETURN
CONCATENATE ( M, CONCATENATE ( ":", S ) )
 
Following table i had.
 
 
Name   seconds   Mins:sec
Abi          500           18:05
Emi          250           18.05
 

Note  : Mins: sec column wrongly written as an expected output
 
 
Need Help