Converting Decimal time to Time format HH:MM:SS

[Sajid02]

Hi Folk , 

Got stuck in one place wherein i want to convert decimal time values into correct time format like HH:MM:SS

Please find below snip :-

Dax I have tried is :- 

ODI SSC Actual_New 1 =

VAR _TPHnPT =
DIVIDE(
SUM('PBIUSER MAX_WORKORDER'[Property Events Seconds]),
[SSC_Total_Props],
0
)

VAR Duration = _TPHnPT
VAR hours = ROUNDDOWN ( _TPHnPT / 3600, 0 )
VAR minutes = ROUNDDOWN ( MOD ( _TPHnPT, 3600 ) / 60, 0 )
VAR seconds = INT ( MOD ( _TPHnPT, 60 ) )

Return
RIGHT("0" & Hours,2) & ":" & RIGHT("0" & Minutes , 2 ) & ":" & RIGHT("0" & Seconds , 2)

Also , by writing this code , i am facing a new problem with calendar slicers as well , as years are not getting showing correct in table with slicer selection.

Code is not working for me here .

How to solve this ?

Thanks in advance for  the help .

[ppm1]

No need to break it down into hours, minutes, seconds. Please try the approach in this article.

Calculate and Format Durations in DAX – Hoosier BI

Pat

[Sajid02]

@amitchandak @onurbmiguel_ @Greg_Deckler 

Any solution ?

[Greg_Deckler]

@ppm1 See if these help:

Decimal Duration Converter - Microsoft Power BI Community

Chelsie Eiden's Duration - Microsoft Power BI Community

[Sajid02]

Both of the articles are not working in my case

[onurbmiguel_]

Hello @Sajid02 

The value ODI SSC actual is in hours? 

Example, the value 2.00 is 02:00:00 ? 

Can you share a pbix with some dummy values? 

Best regards

Bruno Costa | Solution Supplier

Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 👍

Take a look at the blog: PBI Portugal 

[Sajid02]

Hi @onurbmiguel_ 

The Value is in the form of seconds like 

0.35  is 35 seconds 
1.25 is 1 minute and 25 seconds and so on

[onurbmiguel_]

hello @Sajid02 

One other question , so if you have 1,50 it qill be 00:01:50 ? and if you have 1,90 ?

Best regards

Bruno Costa | Impactful Individual

[Sajid02]

Hi 

If i have 1.90 then it should be 00:02:30 sec 

[onurbmiguel_]

Hi @Sajid02 

The examples that you shared does not make sense.

Can you indicated the output for the following examples: 

0.05 -> 00:00:05 
0.35 -> 00:00:35
1.25 -> 00:01:25
1.50 -> 00:01:50
1.90 -> 00:02:30
2.30 -> 00:02:30
2.80 -> 00:03:20
3.20 -> 00:03:20

65.50 -> 01:05:30
65.30 -> 01:05:30

Best regards

Bruno Costa | Impactful Individual

[Sajid02]

Hi ,

You logic make sense on this part . but just now confused on some part .

Like all these values are cming after a dax i.e

ODI SSC Actual_New =

VAR _TPHnPT =

DIVIDE(

    SUM('PBIUSER MAX_WORKORDER'[Property Events Seconds]),

    [SSC_Total_Props],

    0

)

Return

_TPHnPT
Wherein Property event seconds are a column having values in seconds

SSC_Total_Props is a measure - 

SSC_Total_Props = SELECTEDVALUE('PBIUSER MAX_WORKORDER'[PROPS_TOTAL_ALL])

Props_total_all is again a measure -

Props_Total_all =

VAR

    SST_Props =

           LOOKUPVALUE(

                       Reported_Property_Pop_Count[SST Props],

                       Reported_Property_Pop_Count[Fiscal Year],

                       RELATED('Dim Calendar Shutoff Start'[Fiscal Year])  

                       )

VAR

    CAM_Props =

           LOOKUPVALUE(

                       Reported_Property_Pop_Count[CAM Props],

                       Reported_Property_Pop_Count[Fiscal Year],

                       RELATED('Dim Calendar Shutoff Start'[Fiscal Year])  

                       )

RETURN

   SST_Props + CAM_Props

I am getting those mentioned values after such codes and want to convert the same in HH:MM:SS , may be you are right like this is coming in hrs format .

Please help 

[ppm1]

Here is an example of how to format your variable (in seconds) in your desired format. I commented out the SUM expression to show what yours might look like, and put a hard-coded value from your example above to get a result of 2.30.

TestMMSS =
VAR SumSeconds = 150 //SUM(Table[SecondsColumn])
RETURN
FORMAT ( SumSeconds / ( 24 * 60 * 60 ), "nn.ss" )

Pat

[Sajid02]

Hi 

Tried this code but this is returning the incorrect value , plus it's also creating conflict with Calendar Slicer as Fiscal years getting changed.