Convert from Seconds to Minutes with a DAX Measure
I was recently doing some consulting work for a customer and they had a column which contained seconds and whilst this is useful it is better to have it shown as Minutes and Seconds, which is easier to relate and compare.
Below I will show you how I came up with the DAX measure which converts it from seconds to minutes, and I will be using my Parkrun sample dataset. As shown below I have a table which shows the total seconds
Below is my DAX measure with an explanation on how I got to the measure
Secs to Mins = VAR TimeSecs = SUM ( 'ParkRun'[Total Seconds] )+1 VAR TimeMins = DIVIDE ( TimeSecs, 60 ) VAR JustSeconds = TimeMins  TRUNC ( TimeMins ) VAR JustMinutes = TimeMins  JustSeconds VAR JustSecondsInTime = JustSeconds * 60 VAR MySeconds2 = IF ( JustSecondsInTime < 10, FORMAT ( "0" & JustSecondsInTime, "00" ), JustSecondsInTime ) RETURN VALUE ( JustMinutes & "." & LEFT ( MySeconds2, 2 ) )

Line 1
 This is where I created the measure with a name

Lines 2 – 3
 This is where I am summing up all the Total Seconds

Lines 4 – 5
 I am creating a variable called TimeMins and then dividing the seconds by 60 to get the minutes.

Line 6 – 7
 I am creating a variable called JustSeconds, and I am using the TRUN DAX function, so that this returns the remainder of the variable TimeMins, which is currently stored as a decimal.
 NOTE: The above means that the remainder is stored as decimal point out of 100. So if the remainder is 0.50 that translates to 30 seconds. Which is shown below.

Line 8 – 9
 I am creating a variable called JustMinutes, where all that I want is the Minutes, so I do this by subtracting the TimeMins from JustSeconds

Line 10
 I am creating a variable called JustSecondsInTime, and what I am doing is converting the seconds from a decimal value back to time.
 What this means is that if the decimal value was 0.50, the variable JustSecondsInTime will be converted to 0.30 which represents 30 seconds.

Line 11 – 12
 I am creating a variable called MySeconds2, and what I am doing here is to ensure that if it is less than 10 seconds to add in a zero before the second number. This is so that it will be displayed as expected.
 As shown below you can see where the seconds are less than 10 it is displaying correctly.
 When looking at the image above it does appear that the seconds is not being displayed correctly. This will be resolved in the next step below.

Line 13 – 14
 Now I am closing off the variable section to return the measure.
 I begin with the DAX function called VALUE, this is because I want my DAX to be a measure in my model.
 I then put in my variable called JustMinutes which is the minutes from the seconds that I defined in lines 8 & 9

Next, I put in a dot “.” This is to ensure that the VALUE will still work because it expects a value in the format of 99.22
 If I had to change it from a dot to the colon the VALUE function would return an error.

I get the seconds to display correctly by using the DAX function LEFT ( MySeconds2, 2 )
 What I am doing here is using the LEFT DAX Function to get the first 2 values starting from the left hand side. Which results in displaying the seconds
Now when I take my measure and put it into a table I get the Minutes and seconds displaying as expected.
I found that I really had to use the DAX variables to get this pattern to work as expected. With all the screenshots above, I changed the return function to the particular DAX variable that I was working on to ensure that it was returning the value that I was expecting.
If you have any questions or comments please leave them in the section below. I do hope that you found this useful.
[…] Convert from Seconds to Minutes with a DAX Measure (@GilbertQue) […]
Thank you, explained beautifully
Thank you for the kind words.
Thanks for sharing this information!
How can we use “HH:MM:SS” as an axis value for a line/bar/area chart?
Hi there,
The challenge is that I wanted the result to be a data type of number so that it could be used as a measure.
Would you be looking to rather use the output with hours, mins, secs?
Yes, exactly. Thanks for any help / insight you can share!
Let me take a look a little later and see what I can do
@Michael
you can use a calculated column:
Secs to Time =
VAR Hours = QUOTIENT ( ‘ParkRun'[Total Seconds], 3600 )
VAR Rest = MOD ( ‘ParkRun'[Total Seconds], 3600 )
VAR Minutes = QUOTIENT ( Rest, 60 )
VAR Seconds = MOD( Rest, 60 )
RETURN
TIME ( Hours, Minutes, Seconds )
Thanks Frank, that would most certainly work.
The challenge would be when you want to use it as a measure.
You could do it with the following code below, due to a number value only having one point.
SecMinHour =
VAR TimeSecs =
SUM ( 'ParkRun'[Total Seconds] ) + 1
VAR TimeMins =
DIVIDE ( TimeSecs, 60 )
VAR JustSeconds =
TimeMins  TRUNC ( TimeMins )
VAR JustMinutes = TimeMins  JustSeconds
VAR JustSecondsInTime = JustSeconds * 60
VAR MySeconds2 =
IF (
JustSecondsInTime < 10, FORMAT ( "0" & JustSecondsInTime, "00" ), JustSecondsInTime ) VAR JustHours = VAR HourTest = DIVIDE ( JustMinutes, 60 ) RETURN IF ( HourTest < 1, 0, HourTest ) VAR JustHours2 = DIVIDE ( JustMinutes, 60 ) VAR MoreThan1Hour = IF ( JustMinutes < 60, VALUE ( JustMinutes & "." & LEFT ( MySeconds2, 2 ) ), VALUE ( LEFT ( JustHours, 2 ) & "." & JustMinutes ) ) RETURN MoreThan1Hour
[00:06:24] how can i convert this format of hours to seconds?
another question:
how can i get the average of a colones contain:
[00:06:00]
[00:06:44]
[00:05:00]
[00:03:00]
[00:02:00]
Thank you in advance
Hi there
In your example is that time in Minutes & seconds?
If so I would first change it all to seconds in Power Query, which would then allow me to convert it from seconds to minutes.
For your Average I would have it all in seconds, calculate the average My Average = DIVIDE(SUM(Seconds),COUNTROWS(Table))