Calculate durations for each product category

[Hank_]

Hi everybody!

I'm not very good in DAX (yet!) so every help will be very appreciated.

I am sorry if this is an obvious question, however I red a lot on the community but I didn't find the solution I need.

I need this output, for each id:

id	STEP DURATIONS
1	A	30d 5 h 8 m
1	BB	0d 0 h 1 m
1	CCC	8d 1 h 13 m
2	A	days, hours
2	BB	days, hours
2	CCC	days, hours
3	...	...

From this input table:

id	step	status	date		duration (to be calculated for step and for EACH ID)
1	A	in	11/10/2020 10:04		0:00:00
1	A	out	11/10/2020 10:06		0:02:09
1	BB	in	11/10/2020 10:06		0:00:00
1	BB	out	11/10/2020 10:07		0:01:00
1	A	in	11/10/2020 10:07		0:00:00
1	A	out	11/10/2020 10:08		0:01:03
1	CCC	in	11/10/2020 10:08		0:00:00
1	CCC	out	18/11/2020 11:12		913:03:50
1	A	in	18/11/2020 11:12		0:00:00
1	A	out	18/12/2020 16:18		725:05:30
1	CCC	in	18/12/2020 16:18		0:00:00
1	CCC	out	19/12/2020 16:28		24:10:03
2	A	in	...		...

I tried to calculate duration as a calculated column. To do this I've added two index columns and then combining FILTER and EARLIER I calculated the durations, but I am not able to do it separately for each id... 

How can I do it? Which may be the best approach, DAX formulas, calculated columns, M?

Thanks a lot for any advice 🙂 

[Ashish_Mathur]

Hi,

Based on the information that you have shared in the second table, show the exact figures that you are expecting in the first table.

[Hank_]

Hi Ashish! Thank you for your help.

I've updated the output table, and also the input, with the figures.

[Ashish_Mathur · Accepted solution]

Hi,

Can you live with this?  Download the PBI file from here.

[Hank_]

Hi Ashish, your approach is different from mine: you've pivoted the date column. This is very clever!

So I canceled my calc columns and measures and got into M query to pivot and have two columns for in and out date.

However, when trying to pivot columns, I see the error: There were too many elements in the enumeration to complete the operation.

Couldn't find out, then I used your magic steps and finally I got my output table! 

THANK YOU!

[Ashish_Mathur]

You are welcome.