Forum Discussion
Dynamic sum Datetime difference
Hi all,
I need to calculate the sum of timedifference in below scenario:
I need to calculate the sum of difference between start date & end date of step = A (take minimum start date for each type) and Step = B (take minimum end date for each type). for example: difference between start date & end date of row 1 and 2, and difference between start date & end date of row 4 and 6. Record inside step are static (always A, B) but type can be more. In this case it will be 01:10:00
I am having difficulties due to blank values in start date and end date. Not able to pick the correct record.
can anyone give some suggestion?
Thanks
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
9 Replies
Hi TaroGulati
Can you paste the data here ?? or share excel file with expected output. We want to know the exact output you are looking for..
I would be easier for us to find the proper solution if you provide output column as well .Thanks!
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
Column =VAR _start=CALCULATE(min('Table'[Start Date]),ALLEXCEPT('Table','Table'[Type]))var _end=CALCULATE(MIN('Table'[End Date]),ALLEXCEPT('Table','Table'[Type]))return _end-_startpls see the attachment below
