Sum of Time HH:MM is incorrect

[Anonymous]

I am using an SSAS data source. Within my job time table, I have a column for the duration of a job (whole number) which is the minutes. I then have a calculated column in my SSAS model for the duration as HH:MM, like so:

My issue comes when summing up the HH:MM column inside Power BI. I am using a matrix, if i sum up the whole number column for the mins, it sums up correctly, like so:

This figure of 15 jobs = 2780 mins is correct. 2780 mins should be 46:20 in HH:MM, however when i use my HH:MM column from my SSAS model, it sums it like so:

22:20 is incorrect, it should be 46:20.

Is anyone able to help. The measure that I am using to sum the HH:MM column from my model is:

Time = CALCULATE(SUM('Job Time'[Time in MinSec]), FILTER('FactFSA', FactFSA[Job Number ID]))

and i've set this to Time data type, format HH:NN.

Again, this measure works perfectly for the whole number minute duration if i change it to sum the mins column as opposed to the HH:MM column.

The 'Time in MinSec' column is from my SSAS model and is of data type date HH:MM.

I have seen several different posts on here about summing HH:MM but nothing seems to relate to my issue or help me.

Thanks

[ThxAlot]

When you sum up HH:MM, its data type is duration instead of time (HH:MM). As there's no data type of duration in DAX, time data can't exceed 24.

A workaround I used in one of my projects is to convert the sumup of time to decimal by multiplying 24.

[Anonymous]

Thanks for the reply @ThxAlot 

What data type does this measure need to be? 

[ThxAlot]

Type of General (by default) or Decimal.

[Anonymous]

Apologies @ThxAlot , I'm a bit confused as the times you have used in your measure (16:29, 4:51, 12:46) would sum up to 34:06, but your screenshot shows it added up to 34.10 which is decimal for 34:06. I would obviously need to show the time's summed up with a correct HH:MM outcome as oppsoed to the user viewing the time as a decimal? The assumption would be made in your example that the three jobs summed up 34mins 10 secs which isn't correct?

[ThxAlot]

[Anonymous]

This works, up until the sum hits 24 hours.. e.g. the following all sum up correctly to 21:35:

But, when the next job is pulled through which is 04:15 and takes it over 24hrs, the overall sum breaks and goes to 01:50 (assume the total of it all is 24:00 + 01:50):

[Anonymous]

HI @Anonymous,

In power bi data mode table current it does not support duration types. I'd liek to suggest you use two fields to store the diffrent part of duration values(one field store numeric day level, and the second store time values) or convert it to ‘total second’.(you can create a formula to convert them to duration format text values)

Regards,

Xiaoxin Sheng