Total Duration calculation in DAX

[paw1]

Hi,

Please can you help me to calculate Total Duration(Completed-Started) in days hh:mm:sss format aggregated by Department and ID in Dax only. In my reprot Department, ID is coming from one table and Timestamps are coming from another table.

Department	ID	Competed	Started
Clothes	203	31/01/2023 14:45:15	30/01/2023 14:12:15
Clothes	203	19/12/2022 09:57:58	19/12/2022 09:57:30
Electronics	206	21/12/2022 10:57:58	19/12/2022 10:57:58
Electronics	207	22/12/2022 10:57:58	21/12/2022 10:57:58
Electronics	207	26/12/2022 12:57:58	26/12/2022 10:57:58

[Ahmedx]

can show what result you expect

[paw1]

Hi .

I am expecting below Total duration as expected output then it need to sum at department and id level

Department	ID	Total Duration
Clothes	203	1 day 00:33:28
Electronics	206	2 day 00:00:00
Electronics	207	1 day 02:00:00

Department	ID	Competed	Started	Total Duration
Clothes	203	31/01/2023 14:45:15	30/01/2023 14:12:15	1 day 00:33:00
Clothes	203	19/12/2022 09:57:58	19/12/2022 09:57:30	0 day 00:00:28
Electronics	206	21/12/2022 10:57:58	19/12/2022 10:57:58	2 day 00:00:00
Electronics	207	22/12/2022 10:57:58	21/12/2022 10:57:58	1 day 00:00:00
Electronics	207	26/12/2022 12:57:58	26/12/2022 10:57:58	0 day 02:00:00

[Ahmedx]

check my solution
https://dropmefiles.com/okXhV

[paw1]

Hi Ahmedx,

Thanks for you reply. I need aggregate of Total Duration by Department and ID. For example in above ooutput

for Clothes Department Total duration need to show like 1 day 01:33:28 . its sum of two entries in front end table

[Ahmedx · Accepted solution]

check solution
https://dropmefiles.com/6fcgE

[Idrissshatila]

Hello @paw1 ,

check if this function would help for your case https://learn.microsoft.com/en-us/dax/datediff-function-dax

If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍

Follow me on Linkedin

[paw1]

hi Date difference function sytnax contains any one parameter like hour/minute/second. But i am looking for days hh:mm:ss format and also need to aggregate by Department and id