CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
user19674077
user19674077

Reputation:

How can I create a DAX measure to add time in hh:mm format?

I am working with the following data:

Letter  Duration
A       04:40
A       05:20
B       01:20
B       05:43
C       06:34
C       02:45
D       09:21
D       10:12
E       04:39
E       05:30

What I would like to be able to do is to find the total duration for each letter but to keep it in the hh:mm format. I'd like the hh to be able to exceed 24. I would like to plot this in a matrix. However when I try to do this it appears that I cannot. Here is a link to the .pbix file:

https://www.mediafire.com/file/8acsm9irdvoxtcv/Adding_Times.pbix/file

Thanks in advance.

Upvotes: 0

Views: 898

Answers (2)

Umut K
Umut K

Reputation: 1390

try to add a new calculated column as a text format

DAX stores a date as a decimal number, where the integer part is the number of days elapsed since December 30, 1899 and the decimal part is the fraction of the day (6 hours = 0.25, 12 hours = 0.50, and so on).

Balance =
VAR balans =
    CALCULATE (
        MAX ( 'Table'[Duration] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            EARLIER ( 'Table'[Letter] ) = 'Table'[Letter]
        )
    )
        - CALCULATE (
            MIN ( 'Table'[Duration] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                EARLIER ( 'Table'[Letter] ) = 'Table'[Letter]
            )
        )
RETURN
    FORMAT ( INT ( balans * 24 ), "00" ) & ":"
        & FORMAT ( INT ( MINUTE ( balans ) ), "00" ) & ":"
        & FORMAT ( INT ( SECOND ( balans ) ), "00" )

Sample File If you need

Edit : This gives the difference, not the total sum )))

Upvotes: 1

Mik
Mik

Reputation: 2103

dur = 
VAR TotalDuration=
        SUMX(
            Sheet1
            ,VALUE(Format([Duration] ,"hh"))+VALUE(Format([Duration] ,"nn"))/60)
VAR Hours = INT(TotalDuration)
Var Minutes = (TotalDuration - Hours)* 60
        RETURN
        Hours & ":" & FORMAT(Minutes,"0#")

Upvotes: 0

Related Questions