topic Re: how do I calculate the total working time per day, per week, per year in DAX Commands and Tips

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

Anonymous — Mon, 27 Nov 2023 09:36:55 GMT

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:

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

speedramps — Mon, 27 Nov 2023 19:55:54 GMT

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.

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

Anonymous — Mon, 27 Nov 2023 21:22:43 GMT

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.

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

Anonymous — Mon, 04 Dec 2023 14:59:56 GMT

I have a solution.