how do I calculate the total working time per day, per week, per year

[Anonymous]

I have a table with different times for different technicians each day. I would like to calculate the total worked time per day per technician, per week, per month, and per year. What is the correct DAX formula for this? I already have several calculations. The total time per day per technician seems to be correct. However, when I try to display this in a matrix table per week or per month, it doesn't sum up the times. It keeps looking at the first time and the last time per period. Who has a suitable solution?

Example:

[speedramps · Accepted solution]

Does you column have a time data type?

Time data types have a maximum of 24hrs. 

So if you add 23 + 2 = 1 and not 25!

And 8 + 8 + 8 + 8 + 8 = 16 and not 40.

Try using a decimal data type.

[Anonymous]

To find the first time of the day per technician:

• Min Time StartArbeid = MIN(PTS_Z14IT_CRM_HR_DAT[StartArbeid])
• Min Time StartReisHeen = MINX(FILTER(PTS_Z14IT_CRM_HR_DAT,PTS_Z14IT_CRM_HR_DAT[StartReisHeen] <> 0 ),PTS_Z14IT_CRM_HR_DAT[StartReisHeen])
• Min Time = MINX({[Min Time StartReisHeen],[Min Time StartArbeid]},[Value])

To find the last time of the day per technician:

• Max Time TijdEindeArbeid = MAX(PTS_Z14IT_CRM_HR_DAT[EindeArbeid])
• Max Time EindeReisTerug = MAX(PTS_Z14IT_CRM_HR_DAT[EindeReisTerug])
• Max Time = MAXX({[Max Time EindeReisTerug],[Max Time TijdEindeArbeid]},[Value])

To find the total working time per day per technician:

• Totale tijd min = CALCULATE(DATEDIFF([Min Time],[Max Time],MINUTE)) //
• Totale tijd uur = [Totale tijd min]/60

Then the ‘Totale tijd min’ provides the total working time in minutes per day. When I place this as a value in a matrix table, I get an incorrect calculation of the total time. Also, when I try to see the total working time per week, I do not get accurate results.

[Anonymous]

I have a solution.