Change to mm:ss format

[Anonymous]

I have connected to excel data source in format of mm:ss which shows up as the following in power bi but want to show it as mm:ss and want to show that value in a card and in a chart and plot against month

[v-luwang-msft · Accepted solution]

Hi @Anonymous ,

Refer the below sample (adjust from second to dd.hh:mm:ss,adjust it according your need，you could adjust your time  to second，through *60 to get it):

Measure = 
VAR ss =
    SUM ( 'test'[seconds] )
VAR mm =
     ( ss - MOD ( ss, 60 ) ) / 60
VAR hh =
     ( mm - MOD ( mm, 60 ) ) / 60
VAR mmm =
    mm - MOD ( mm, 60 )
VAR res = ss - hh * 3600
VAR rem =
     ( res - MOD ( res, 60 ) ) / 60
VAR ress = ss - rem * 60 - hh * 3600
RETURN
    hh & ":" & rem & ":" & ress

Best Regards

Lucien

[Shaurya]

Hi @Anonymous,

Let me know if I'm interpreting this correctly. You want to round the values to two decimal places like 1.02 and show it. You can do that easily in DAX by setting the number of places after decimal.

Or do you want to show these values as 01 mm : 02 ss ?

The later can be achieved by splitting the numbers and using CONCATENATE to add mm and ss.

Did I answer your question? Mark this post as a solution if I did!
Consider taking a look at my blog: Forecast Period - Previous Forecasts