Forum Discussion
Helper IIIDynamic sum Datetime difference
you can try this
Column =VAR _start=minx(FILTER('Table','Table'[Category]=EARLIER('Table'[Category])&&'Table'[Step]="A"&&'Table'[Type]=EARLIER('Table'[Type])),'Table'[Start Date])var _end=minx(FILTER('Table','Table'[Category]=EARLIER('Table'[Category])&&'Table'[Step]="B"&&'Table'[Type]=EARLIER('Table'[Type])),'Table'[Start Date])return if ('Table'[Start Date]=_start,_end-'Table'[Start Date])pls see the attachment below
Helper IIIHi, Thanks for the response.
Solution purposed by you work but not completly in my case. Let me show you the complete case because in the above part i asked only the one part. As you can see below we have 4 columns: Category, Step, Type and start date. For each category i need to calculate start date differences. For each type (X,Y it can vary) take minimum start date for step A and minimum start date for step B. Once we have date difference for each category i need to calculate the sum and average based on types but should be easy. In the below mentioned case the sum for category ABC is 1 hour 10 minutes and for category XYZ is 40 minutes.
| Row ID | Category | Step | Type | Start Date | Expected Result |
| 1 | ABC | A | X | 19-12-2024 11:10 | Time Difference is 1 Hour |
| 2 | ABC | B | X | 19-12-2024 12:10 | |
| 3 | ABC | B | X | 19-12-2024 12:15 | |
| 4 | ABC | A | Y | 19-12-2024 12:20 | Time Difference is 10 minutes |
| 5 | ABC | A | Y | 19-12-2024 12:25 | |
| 6 | ABC | B | Y | 19-12-2024 12:30 | |
| 7 | XYZ | A | X | 20-12-2024 15:00 | Time Difference is 10 minutes |
| 8 | XYZ | B | X | 20-12-2024 15:10 | |
| 9 | XYZ | B | X | 20-12-2024 15:30 | |
| 10 | XYZ | A | Y | 20-12-2024 15:35 | Time Difference is 30 minutes |
| 11 | XYZ | A | Y | 20-12-2024 16:00 | |
| 12 | XYZ | B | Y | 20-12-2024 16:05 |
I highlighted the rows:
Thanks in advance for the response
Super User
you can try this
- TaroGulati
Hi, this is working.
Thanks
- ryan_mayu
you are welcome