Forum Discussion

xoptopus's avatar
xoptopus
Regular Visitor
1 year ago
Solved

Hour proccess

Hi,

I add hour values ​​in decimal with a measurement. A figure like the one below comes out. How can I show this as hours, which is higher than 24 hours? For example, I want to show the value in the measurement 130:25 (HH:mm) as hours.

 

Need Help
  • xoptopus's avatar
    xoptopus
    1 year ago

    Hi kushanNa 

    Thanks for reply. It's not working. It should be more than 130 hour but result it seems123 hour.

     

     

  • xoptopus's avatar
    xoptopus
    1 year ago

    Hi kushanNa 

    The command didn't actually give me the exact results I wanted. I have 8 columns. The formula in each is: duration time (ex: 23:59) * 24. I need to add these 8 columns together. I tried your command with decimal, but the correct results were not obtained. Do you have experience with time operations on this subject? Because I don't have any experience and I read and tried articles on the internet, but they didn't work for me. I really need help on this subject.

12 Replies

  • Deku's avatar
    Deku
    Super User
    1 year ago

    So is that the sum( table[hour] ) , where a value 1.5 in table[hour] equals 1:30 (HH:mm) ?

     

     

    var hourPart= int( [hours] ) 
var minutePart= ( [hours] - hourPart ) * 60 
return
hourPart & ":" & minutePart

     

     

    • xoptopus's avatar
      xoptopus
      Regular Visitor
      1 year ago

      Hi Deku 

      My measure is below
      Duration_Hours =
      VAR StartTime = TIMEVALUE([BASSAAT])  
      VAR EndTime = TIMEVALUE([BITSAAT])  
      RETURN DATEDIFF(StartTime, EndTime, MINUTE) / 60

      the result of this process 130,97

      I wrote your command but the result appeared as 00:15.

       

      • kushanNa's avatar
        kushanNa
        Super User
        1 year ago

        try this mesure , replace table name with yours 

         

        Total_Duration_Hours_Measure = 
VAR TotalDurationInMinutes = 
    SUMX(
        'Table (2)',  -- Replace with your actual table name
        DATEDIFF(TIMEVALUE('Table (2)'[BASSAAT]), TIMEVALUE('Table (2)'[BITSAAT]), MINUTE)
    )
VAR TotalHours = QUOTIENT(TotalDurationInMinutes, 60)
VAR TotalMinutes = MOD(TotalDurationInMinutes, 60)
RETURN FORMAT(TotalHours, "00") & ":" & FORMAT(TotalMinutes, "00")

         

  • sanalytics's avatar
    sanalytics
    Super User
    1 year ago

    xoptopus 

    Some thing like below screenshot?

    if it is then below is the code

    Hours : Min = 
VAR _Hoursmin = 60
VAR _HoursCalc = 
INT(
DIVIDE( SUM( 'Table'[Number] ),100))
VAR _ExtraHours = 
INT(
DIVIDE( MOD(
SUM( 'Table'[Number] ),100), _Hoursmin )
)
VAR _Hours = _HoursCalc + _ExtraHours
VAR _Minute = 
INT(
MOD( SUM( 'Table'[Number] ),100 ) - _Hoursmin )
VAR _Result = 
CONCATENATE(
CONCATENATE(
CONVERT( _Hours,STRING ),":"
),CONVERT(_Minute,STRING ) )
RETURN
_Result


    Hope it helps

    Regards

    sanalytics

    If it is your solution the please like and Accept it as your solution

    • xoptopus's avatar
      xoptopus
      Regular Visitor
      1 year ago

      Hi sanalytics 

      Thanks for your answer. However, I want to use the total within the measure, not the total of a column in the table. How can I use the command you sent for the measure?

      • Deku's avatar
        Deku
        Super User
        1 year ago

        Replace the reference to table[column] to the [measure]