Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

About Time Calculation

Hello,

 

How can I sum the above times. I want to use the results on the chart. The result should be 01:05

 

Regards,

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Currently, we couldn't add measure with Time or Text format data into value field in some charts like bar chart, line chart and so on. If your data is duration format in Power Query Editor, and data format in report value is Time, you can try sum or sumx to sum your time.

    Measure = SUMX('Table',[Column1])

    Here I suggest you to convert your Time format data to number format like hour, minute or second. Then you can succeed to use it in value field in bar charts.

    In my sample, I convert time to minute.

    MINUTE = 
VAR _HOUR = SUMX('Table',HOUR('Table'[Column1]))*60
VAR _MINUTE = SUMX('Table',MINUTE('Table'[Column1]))
RETURN
_HOUR+_MINUTE

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

10 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Hi Anonymous ,

     

    Create a measure with below code:-

     

    Measure 2 = 
VAR TotalSeconds =
    SUMX (
        'Minutes_data',
        HOUR ( 'Minutes_data'[Minuts] ) * 3600
            + MINUTE ( 'Minutes_data'[Minuts] ) * 60
    )
VAR Days =
    TRUNC ( TotalSeconds / 3600 / 24 )
VAR Hors =
    TRUNC ( ( TotalSeconds - Days * 3600 * 24 ) / 3600 )
VAR Mins =
    TRUNC ( MOD ( TotalSeconds, 3600 ) / 60 )
RETURN
    IF ( DAYS = 0, "", IF ( DAYS > 1, DAYS & "days ", Days & "day" ) )
        & IF ( Hors < 10, "0" & Hors, Hors ) & ":"
        & IF ( Mins < 10, "0" & Mins, Mins )

     

     

    Output:-

     

     

    Thanks,

    Samarth

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you this worked. But I want to graph these results on a chart. I guess the chart doesn't accept it because it's a measure. How can I fix this problem?

      • Samarth_18's avatar
        Samarth_18
        Community Champion
        4 years ago

        Anonymous ,In which chart you are using it?