Duration displaying days : hours : minutes : seconds using Chelsie Eiden's Duration

[locka]

Good morning everyone,  

I get a lot of great help on here hope for some more  I've been using following calculation to display duration for a while and it works well. 

Chelsie Eiden's Duration = 
// Duration formatting 
// * @konstatinos 1/25/2016
// * Given a number of seconds, returns a format of "hh:mm:ss"
//
// We start with a duration in number of seconds
VAR Duration = SUM([Duration])
// There are 3,600 seconds in an hour
VAR Hours = INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
RETURN
// We put the hours, minutes and seconds into the proper "place"
Hours * 10000 + Minutes * 100 + Seconds

There are two versions on her one display Hours:Minutes:Seconds and another displaying Days:Hours:Minutes. 

Chelsie Eiden's Duration - Microsoft Fabric Community

What I would like to do is have a version which has Days:Hours:Minutes:Seconds.  This is what I've written  but it's not correct is there any way using the above code rather than different calculation.  That I can get this to work then I can add a switch statement and drop days or seconds  depending on the length of the duration

_AgentEventDuration (DD.HH:MM:SS) = 
VAR Duration = sum(AC_FACT_AgentEvents[Duration]) // There are 3,600 seconds in an hour

VAR Days = 
    INT ( 
        Duration 
           / 
           86400
     ) // There are 86,400 seconds in an day

VAR Hours =
    INT ( 
        Duration 
        / 
        3600 
    ) // There are 60 seconds in a minute

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

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

VAR Result = 
Days * 10000 + Hours * 100 + Minutes *10 + seconds

RETURN
    Result

[locka]

Hi still looking at as it was only updated Thursday and have other work to content with shall get back asap

[Anonymous]

Hi @locka,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi  @locka,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Regards,

Vinay Pabbu

[Anonymous]

Hi @locka,

We noticed there hasn’t been any recent activity on this thread. If your issue is resolved, marking the correct reply as a solution would be a big help to other community members.
If you still need support, just reply here and we’ll pick it up from where we left off.

Regards,

Vinay Pabbu

[kushanNa]

Hi @locka 

Maybe this is what you need ? 

New Chelsie Eiden's Duration = 
// Duration formatting 
// * @konstatinos 1/25/2016
// Given a number of seconds, returns a format of "dd:hh:mm:ss" as integer DDHHMMSS

VAR Duration = SUM([Duration]) // total seconds

// Constants
VAR SecondsInMinute = 60
VAR SecondsInHour = 3600
VAR SecondsInDay = 86400

// Calculate each unit
VAR Days = INT(Duration / SecondsInDay)
VAR Hours = INT(MOD(Duration, SecondsInDay) / SecondsInHour)
VAR Minutes = INT(MOD(Duration, SecondsInHour) / SecondsInMinute)
VAR Seconds = ROUNDUP(MOD(Duration, SecondsInMinute), 0) // We round up to get a whole number

RETURN
Days * 1000000 + Hours * 10000 + Minutes * 100 + Seconds

once you create the mesure change the mesure format into following format 

[locka]

@kushanNa Thanks for your reply.  Tried your fix which is fine if I have 00:00:00:00 format.  What I'm looking for is to be able to use dax to do following : -

IF(
    Duration >= 86400 ,
          Days * 1000000 + Hours * 10000 + Minutes * 100,
          Hours * 10000 + Minutes * 100 + Seconds
)

So basically it caters for both under and over a day slightly differently 

[kushanNa]

Hi @locka 

If you check on the original measure you can see he had used a fixed format 00:00:00 , but you have mentioned you want to use the above code , so if you want to use a measure similar to it you will not be able to use it and switch between formats 

you can use the following measure if you want to dynamically switch between formats 

Formatted Duration :=
VAR TotalSeconds = SUM([Duration])
VAR Days = INT(TotalSeconds / 86400)
VAR Hours = INT(MOD(TotalSeconds, 86400) / 3600)
VAR Minutes = INT(MOD(TotalSeconds, 3600) / 60)
VAR Seconds = MOD(TotalSeconds, 60)

RETURN
IF(
    TotalSeconds < 86400,
    FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00"),
    FORMAT(Days, "00") & ":" & FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Seconds, "00")
)

[locka]

Thanks for your reply and trying to help.   

However the whole reason for using the original example is it keeps the duration as a number.  Using format function turns everything to a string.  Going down that route would be much easier but completely defeats the objective. 

I thinking I might have my own a solution just testing

[kushanNa]

Hi @locka 

Oh okay , I think now I get your requirement 

check if this solution works 

create this measure 

3.Chelsie Eiden's Duration = 
// Duration formatting 
// * @konstatinos 1/25/2016
// * Updated 6/9/2025 with Days variable
// * Given a number of seconds, returns a format of "ddhhmmss" (Days + hh:mm:ss)
//
// We start with a duration in number of seconds
VAR Duration = SUM([Duration])
// Calculate Days (there are 86400 seconds in a day)
VAR Days = INT(Duration / 86400)
// Calculate remaining seconds after removing days
VAR RemainingAfterDays = MOD(Duration, 86400)
// Calculate Hours
VAR Hours = INT(RemainingAfterDays / 3600)
// Calculate Minutes
VAR Minutes = INT(MOD(RemainingAfterDays, 3600) / 60)
// Calculate Seconds (rounded up)
VAR Seconds = ROUNDUP(MOD(MOD(RemainingAfterDays, 3600), 60), 0)
RETURN
// Format as Days * 1000000 + Hours * 10000 + Minutes * 100 + Seconds
Days * 1000000 + Hours * 10000 + Minutes * 100 + Seconds

go to measure format and change it to dynamic and add this code 

IF(SELECTEDMEASURE()< 1000000 , "00:00:00","00:00:00:00")

[burakkaragoz]

Hi @locka ,

The issue you’re facing with the percentages (VM% and GM%) in your P&L matrix is a common one in Power BI when working with unpivoted tables. When you use calculated columns for percentages, Power BI aggregates the numerators and denominators separately, which can lead to incorrect percentages at higher levels of your matrix.

Recommended Solution:
To get the correct percentage for each row relative to the total, you should use a DAX measure instead of a calculated column. This way, Power BI calculates the percentage dynamically based on the context of your matrix. For example:

dax

VM% P&L = DIVIDE(
    CALCULATE(SUM('Table'[Variable Contribution])),
    CALCULATE(SUM('Table'[Net Sales]))
)

dax

GM% P&L = DIVIDE(
    CALCULATE(SUM('Table'[Gross Profit P&L])),
    CALCULATE(SUM('Table'[Net Sales]))
)

With these measures, Power BI will recalculate the numerator and denominator according to your matrix filters, showing the correct percentage at every level.

Steps:

1. Create these measures in your model.
2. Use the measures in your matrix instead of custom columns.
3. If you need to display the % of the overall total, make sure your matrix context allows it (for example, by using "Show values as % of grand total").

Useful Reference:

• DAX Basics in Power BI (Microsoft Docs)

If you need more detailed DAX examples based on your model, just let me know!
If this helps, please consider marking as solution and giving kudos to help others in the community.

Thank you!
translation and formatting supported by AI

[locka]

Apologies but I'm really confused are you sure this is correct post for this comment I'm doing durations not % this has nothing remotely to do with my post unless I'm completely missing something. 

It is still early here in England and I've not had proper coffee yet 😊  So I might have missed the point