Forum Discussion
Dynamic 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
Hi Here is the data and expected result in the last column:
| Row Number | Step | Type | Start Date | End Date | Expected Result |
| 1 | A | X | 19-12-2024 11:10 | Row number 2 - Row number which is 1 hour | |
| 2 | B | X | 19-12-2024 12:10 | ||
| 3 | B | X | 19-12-2024 12:15 | ||
| 4 | A | Y | 19-12-2024 12:20 | Row number 4 - Row number 6 which is 10 minutes | |
| 5 | A | Y | 19-12-2024 12:25 | ||
| 6 | B | Y | 19-12-2024 12:30 |
pls see if this is what you want
Hi, 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
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 belowHi, this is working.
Thanks