dax

[Anonymous]

Hi,

Convert HH:MM:SS to Minutes format and Minutes to HH:MM:SS Duartion format.

Example: 452:55:60 to Minutes

Regards.

[v-kelly-msft]

Hi @Anonymous ,

As what have said by @amitchandak 

To convert HH:MM:SS(which is a text format) to Minutes format,  you need to create  a calculated column, which is as below:

Minutes =
var a = LEFT('Table'[Date],LEN('Table'[Date])-6)
var b = MID('Table'[Date],LEN('Table'[Date])-(LEN('Table'[HH])+1),2)
var c = RIGHT('Table'[Date],2)
Return
a*60+b+c/60

To convert HH:MM:SS(which is a date format) to Minutes format, you need to create another calculated column, which is as below:

Minutes = 
var a =FORMAT('Table (2)'[Date2],"HH")
var b =FORMAT('Table (2)'[Date2],"mm")
var c=FORMAT('Table (2)'[Date2],"ss")
return
a*60+b+c/60

As for converting Minutes to HH:MM:SS, you can only convert Minutes to HH:MMif the minute is a whole number, or convert Minutes to HH:MM:SS format when the minute is a decimal number, such as 356.2 Minutes to HH:MM:SS,you need to create the following calculated columns:

1.convert Minutes which is  a whole number to HH:MM,

Get minute = MOD('Table (2)'[Minutes],60)
Get hour = DIVIDE('Table (2)'[Minutes],60)

2.convert Minutes which is  a decimal number to HH:MM:SS,

ss = ('Table (3)'[Minutes 2]-ROUND('Table (3)'[Minutes 2],0))*60
MM = mod(ROUND('Table (3)'[Minutes 2],0),60)
HH = DIVIDE('Table (3)'[Minutes 2],60)

For the related .pbix file,you can turn to the URL:

https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-kellya_microsoft_com/ES2gD4Ns3PFBqoAx3s_z5rsByfWWPbE8qxwU6ok8YpLWPQ?e=9v9xCx

Hope this would help.

Best Regards,

Kelly

[Anonymous]

Hello Kelly,

Trying the same but getting this error pls help, when trying LEN & Left independently its working

Regards.

[v-kelly-msft]

Hi @Anonymous ，

Left function is used for a text type data, be sure that the column of “Response time (H:M:S)” is a text type data, not a  date type.

If the column Response time (H:M:S) is a date type, please create a calculated column below:

Minutes =
var a =FORMAT('Table (2)'[Date2],"HH")
var b =FORMAT('Table (2)'[Date2],"mm")
var c=FORMAT('Table (2)'[Date2],"ss")
return
a*60+b+c/60

Thanks.

Best Regards,

Kelly

[Anonymous]

Hi Kelly,

Thanks for your time. ideally, i wish to calcualte the Avearge in HH:MM;SS format.

Ist possible?

[v-kelly-msft]

Hi @Anonymous ,

What you need  is to create a measure as below:

 
Average = AVERAGE('Table'[Minutes])

For the related .pbix file, you can turn to the URL below:

https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/EaetS39v0VpHoMJPbziSIMIB-5ExcKzSlzT9bShby_QCXw

Hope this would help.

Best Regards,

Kelly

[Anonymous]

Hi Kelly,

Please helpp on this.

Please find the attched source and PbIX and help me to find the average time.

REgards,

[Anonymous]

Sorry,

PFA

https://drive.google.com/drive/folders/1NUK39OZ2axFLQ9NDUYAX2ocfBFWzE1ZX?usp=sharing 

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

You need to create 3 calculated columns to get the value of “HH”,”MM”and “SS” first, the related dax expressions are listed below:

HH = IF('Sheet1'[Position 1]=0,BLANK(),LEFT('Sheet1'[Resolution Time (H:M:S)],'Sheet1'[Position 1]-1))

MM = IF('Sheet1'[Position 1]=0||'Sheet1'[Position 2]=0,"0",
MID('Sheet1'[Resolution Time (H:M:S)],'Sheet1'[Position 1]+1,'Sheet1'[Position 2]-('Sheet1'[Position 1]+1)))

SS = RIGHT('Sheet1'[Resolution Time (H:M:S)],LEN('Sheet1'[Resolution Time (H:M:S)])-'Sheet1'[Position 2])

Finally,create a measure to get the value of average:

 measure = AVERAGE(Sheet1[Minutes])

For the related .pbix file, pls click here.

Best Regards,

Kelly

[Anonymous]

Hi Kelly,

The Data shows as text but could not able to use the same. may i know why a new column with"Minute" has been created in the attachment?

Does it has any importance?

If u wish i can attach the Pbx.

Regards.

[amitchandak]

if it market as datetime or time

min = format(col1,"HH")*60 +format(col1,"MI")+ +format(col1,"SS")/60

If it is a text

Min = left(col,2)*60 + mid(col,3,2) + right(col,2)/60

In case there more than 99 hours , use search function to find the position of :

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...


https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601