Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Hank_
Helper I
Helper I

Calculate durations for each product category

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:

 

idSTEP DURATIONS 
1A30d 5 h 8 m
1BB0d 0 h 1 m
1CCC8d 1 h 13 m
2Adays, hours
2BBdays, hours
2CCCdays, hours
3......

 

From this input table:

 

idstepstatusdate duration (to be calculated for step and for EACH ID)
1Ain11/10/2020 10:04 0:00:00
1Aout11/10/2020 10:06 0:02:09
1BBin11/10/2020 10:06 0:00:00
1BBout11/10/2020 10:07 0:01:00
1Ain11/10/2020 10:07 0:00:00
1Aout11/10/2020 10:08 0:01:03
1CCCin11/10/2020 10:08 0:00:00
1CCCout18/11/2020 11:12 913:03:50
1Ain18/11/2020 11:12 0:00:00
1Aout18/12/2020 16:18 725:05:30
1CCCin18/12/2020 16:18 0:00:00
1CCCout19/12/2020 16:28 24:10:03
2Ain... ...

 

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 🙂 

1 ACCEPTED SOLUTION

Hi,

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish! Thank you for your help.

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

Hi,

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.