Duration Measure not maintained after filters and hours not aggregating correctly

[Lisa-Eagle]

I am using the following duration measure obtained via @Greg_Deckler 

***

CE Course Completion Duration =

VAR total =

    SUM ('Course Enrollments'[# of Seconds to Completion] )

VAR days =

    INT ( total / 86400 )

VAR hours =

    INT ( total / 3600 )

VAR minutes =

    INT ( MOD ( total, 3600 ) / 60 )

VAR seconds =

ROUNDUP( MOD ( MOD ( total, 3600 ), 60 ), 0 )

   RETURN

    days & " d "

        &  hours

        &" h "

        & minutes

        & " m "

        & seconds

        & " s"

***   

I am experiencing some issues with my duration and hoping someone can point me in the right direction.

Issue 1 is that I would like to adapt the measure to only show a total of 24 hours as there are 24 hours in a day. 

If we take the last duration as an example - it shows 3 days and 89 hours - I would like the hours to carry over the days if over 24 hours.

When I try to show a total duration I get the following: 

@Greg_Deckler 

Issue 2 is if I select to filter my table by group my duration measure is not maintained and I only get blanks dhms. Since these courses are completed I would like the measure to show as expected.

[Lisa-Eagle]

Thank you for your suggestion. I tried the revised measure but it is not converting seconds correctly. Please see below. The seconds to completion seems to be accurate but conversion to the dhms measure not correct. I would've expected this to be 6 days and not 17 days. Could you please double check the suggested measure.

[v-yiruan-msft]

Hi @Lisa-Eagle ,

I used the same formula in my sample pbix file, it can return the expected result... Please find the details in the attachment.

I doubt that the formula "SUM ( 'Course Enrollments'[# of Seconds to Completion] )" didn't get the correct result actually. Is [# of Seconds to Completion] a field or measure? If it is a measure or calculated column, could you please provide the related formula for it? In addition, could you please share a simplified pbix file with only datetime field for further troubleshooting and give you a suitable solution shortly? You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[v-yiruan-msft]

Hi @Lisa-Eagle ,

Please update the formula of your measure as below and check if it can return the expected result. 

String Duration in Days Hours Minutes and Seconds =
VAR vSeconds =
    SUM ( 'Course Enrollments'[# of Seconds to Completion] )
VAR vMinutes =
    INT ( vSeconds / 60 )
VAR vRemainingSeconds =
    MOD ( vSeconds, 60 )
VAR vHours =
    INT ( vMinutes / 60 )
VAR vRemainingMinutes =
    MOD ( vMinutes, 60 )
VAR vDays =
    INT ( vHours / 24 )
VAR vRemainingHours =
    MOD ( vHours, 24 )
RETURN
    vDays & " d " & vRemainingHours & " h " & vRemainingMinutes & " m " & vRemainingSeconds & " s"

And you can refer the following blog to achieve it:

Calculate Duration in Days Hours Minutes and Seconds Dynamically in Power BI using DAX - RADACAD 

And for your issue 2, could you please provide some raw data in the table 'Course Enrollments' with Text format, the Fields setting in your table visual and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards