none
Sum time (Durrations) greater than 24 hours RRS feed

  • Question

  • I am new to Power BI but have been using Excel for well over a decade and using VBA and SQL Server.

    I have spent the best part of a day searching for a simple solution as all the ones I found seem to be to complicated, I must be missing something.

    In excel if you sum a range of Time Values that add up to more than 24 hours it will increment the Integer to represent days and the remainder would be the time. if you wanted to show hours greater than 24 simply format [HH]:MM:SS.

    Trying to do similar in Power BI.

    I have values representing length of time an activity took, I have them in decimal number format that represents time. i.e. I can format that column as time and the values are correct.

    when I try to aggregate that column to say a weeks worth of activity I only every get values up to 23 hours.

    I have tried various methods of breaking the time parts down and then concatenating them back but no joy.

    any advice gratefully received on a solution as I work in a contact centre and most of my reports will have a time dimention.

    TIA

    JAW70

    Friday, August 16, 2019 3:40 PM

Answers

  • In general, if you want to sum time values, you would use an expression like:

    = List.Sum(List.Transform(<PreviousStepName>[Time], Number.From)), where [Time] is the name of the time column.

    The inner (transform) function converts the time values to numbers, and the outer function sums the values. I imagine that you will be summing by some grouping criteria (week?) to provide context for the summation.

    Friday, August 16, 2019 5:55 PM

All replies

  • In general, if you want to sum time values, you would use an expression like:

    = List.Sum(List.Transform(<PreviousStepName>[Time], Number.From)), where [Time] is the name of the time column.

    The inner (transform) function converts the time values to numbers, and the outer function sums the values. I imagine that you will be summing by some grouping criteria (week?) to provide context for the summation.

    Friday, August 16, 2019 5:55 PM
  • We can provide general tips here, but can't provide a specific solution as the question is not specific.

    If you have a column of type time then you can convert it to a column of type duration. but you can't just right click on the column and change the type to duration - you'll get an error. Instead you transform the column or add a new column  E.g. you would have to do something like:

    Table.AddColumn(<PreviousStepName>, "Duration", each [a]-Time.From(0), type duration)

    As when you subtract 2 values of type time from one another, you'll get a duration. 

    The value of the duration type can be of many days.

    Duration type values can also be added to one another just like the number type and hence List.Sum works with both of these types. 


    Saturday, August 17, 2019 5:08 AM