Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Time Calculation on Power BI

Hello,

 
 

https://i.hizliresim.com/3BQozh.jpg

How can I calculate total time the Minutes in the column?

 

Regards

 

Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    Sure, try this:

    Total Time = 
VAR Seconds1 =
    SUMX ( 'ARRET', SECOND ( ARRET[Sure] ) )
VAR Minutes1 =
    SUMX ( 'ARRET', MINUTE ( ARRET[Sure] ) )
VAR Hours1 =
    SUMX ( 'ARRET', HOUR ( ARRET[Sure] ) )
VAR Seconds2 =
    MOD ( Seconds1, 60 )
VAR Minutes2 =
    MOD ( TRUNC ( Seconds1 / 60 ) + Minutes1, 60 )
VAR Hours2 =
    Hours1
        + TRUNC ( ( TRUNC ( Seconds1 / 60 ) + Minutes1 ) / 60 )
RETURN
    Hours2 & ":"
        & FORMAT ( Minutes2, "00" ) & ":"
        & FORMAT ( Seconds2, "00" )

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

6 Replies

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You can try to create a Measure like so:

    Total Time = 
VAR Seconds1 =
    SUMX ( 'Table_', SECOND ( Table_[Time_] ) )
VAR Minutes1 =
    SUMX ( 'Table_', MINUTE ( Table_[Time_] ) )
VAR Hours1 =
    SUMX ( 'Table_', HOUR ( Table_[Time_] ) )
VAR Seconds2 =
    MOD ( Seconds1, 60 )
VAR Minutes2 =
    MOD ( TRUNC ( Seconds1 / 60 ) + Minutes1, 60 )
VAR Hours2 =
    Hours1
        + TRUNC ( ( TRUNC ( Seconds1 / 60 ) + Minutes1 ) / 60 )
RETURN
    Hours2 & ":"
        & FORMAT ( Minutes2, "00" ) & ":"
        & FORMAT ( Seconds2, "00" )

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Can you write the measur codes according to my column names? because I couldn't 😞

       

      Table name: ARRET

      Coulm name: Sure

       

      Thank you 

       

      • Icey's avatar
        Icey
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Sure, try this:

        Total Time = 
VAR Seconds1 =
    SUMX ( 'ARRET', SECOND ( ARRET[Sure] ) )
VAR Minutes1 =
    SUMX ( 'ARRET', MINUTE ( ARRET[Sure] ) )
VAR Hours1 =
    SUMX ( 'ARRET', HOUR ( ARRET[Sure] ) )
VAR Seconds2 =
    MOD ( Seconds1, 60 )
VAR Minutes2 =
    MOD ( TRUNC ( Seconds1 / 60 ) + Minutes1, 60 )
VAR Hours2 =
    Hours1
        + TRUNC ( ( TRUNC ( Seconds1 / 60 ) + Minutes1 ) / 60 )
RETURN
    Hours2 & ":"
        & FORMAT ( Minutes2, "00" ) & ":"
        & FORMAT ( Seconds2, "00" )

         

         

        Best Regards,

        Icey

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for answer.

      Report in direct query mode from SQL database.  How should i do?