Forum Discussion
Time difference based on two key columns
Hi Elirot
You may try to use DATEDIFF Function as below. For example:
Measure =
VAR a =
CALCULATE (
MAX ( Table[CompleteDate] ),
VALUES ( Table[Team] ),
ALL ( Table )
)
VAR b =
CALCULATE (
MIN ( Table[CompleteDate] ),
VALUES ( Table[Team] ),
ALL ( Table )
)
VAR c =
CALCULATE ( COUNTROWS ( Table), ALLEXCEPT ( Table, Table[Team] ) )
RETURN
DATEDIFF ( b, a, SECOND ) / c
Regards,
Cherie
Hi Cherie,
Thank you for your response. I guess I didn't explain myself right.
What you've actually calculated is the average time for each row for each team.
From your example the time difference for team 'a' and AccountAppId '1' between the min CompletedDate and the max is 60 seconds.
Now, any team might appear many times, so I need to know the time difference for each team for each instance it appears, and then have the average of the multiple time differences, i.e. the average time difference for all instances of each and every team. Let's say in your example team 'a' apperas again and the time difference now is 200 seconds, the average will be 130 seconds.
If you have a look at my data you can see that the Proprety team for instance appears twice. At the first instance the time difference is 49 seconds. At the second instance the time difference is 1228903, so the average is 614476 seconds.
Now, the MAX CompletedDate for each instance is either where the Team has been changed or where the AccountAppId has been changed as can be seen in my example of data.
So it should be something like the sum of all time differences between the min and max CompletedDate for each bulk of rows of each team (bulk or rows is defined as the number of rows between each change of a Team or AccountAppId), divided by the number of groups of rows of each team.
Hope this makes sense.
Cheers
Eli
Hi Elirot
You may try to get a group index column in Query Editor. Then calculate based on the group column. Here is the reference for you.
https://community.powerbi.com/t5/Desktop/dax-grouping-consecutive-days/m-p/488880#M227807
Regards,
Cherie
Hi Cherie,
I've tried the solution you've suggested but it is a difference scenario, i.e. it deals with consecutive dates while my scenario is quite different. It helped me to get the duration between consecutive rows but I still cannot get the full duration between each part (set of rows).
I've attached what I did as It might help to get a solution, so now you can see there is a Duration column as well as Change column, and I need the sum of Durations for each group of rows starting with Change =1 and following with Change = 0 up to the next Change = 1 but not included. Also, with each change between AccountAppId's I set the duration to 0 as with each AccountAppId it should sum up only its elements/rows.
The duration is between the current row Completeddate and the next row.
Thanks again for you help. I really appreciate it.
Eli
Hi Elirot
I do it in Query Editor. So we can use 'Fill down' to get the full index. Show the file as below and hope it can help you. You may try to follow the same steps as below 3 queries.
Regards,
Cherie