Reply
Frequent Visitor
Posts: 15
Registered: ‎06-20-2018

Time difference based on two key columns

Hi guys,

 

I'm strugelling to get the duration in seconds between the minimum CompletedDate and maximum CompletedDate of the same AccountAppId and Team.

If you look at the follwoing image of the data you can see that the first seven rows belong to the same AccountAppId - 102101, and the same Team - Property. The first CompletedDate for this combination is 20/9/2016 9:51:22 AM and the last CompletedDate is 20/9/2016 9:52:11 AM, so the time difference is  49 seconds.

If you continue to the blue arrow you can see that the AccountAppId is different from the first one, so this is a brekpoint as well, i.e. each changing with a Team or AccountAppId is a breakpoint.

Now, to make it more challenging... at the end I need to have the average time for each Team (don't care here on the AccountAppId).

 

I really appreciate your help here as with the DAX functions I played with I couldn't get a logical result.

 

Many thanks

 

Eli

 

WorkflowItem Data.jpg

Community Support Team
Posts: 1,343
Registered: ‎07-25-2018

Re: 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

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 15
Registered: ‎06-20-2018

Re: Time difference based on two key columns

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

Community Support Team
Posts: 1,343
Registered: ‎07-25-2018

Re: Time difference based on two key columns

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

 

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 15
Registered: ‎06-20-2018

Re: Time difference based on two key columns

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

 

WorkflowItem Data.jpg

Community Support Team
Posts: 1,343
Registered: ‎07-25-2018

Re: Time difference based on two key columns

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.

https://www.dropbox.com/s/mmyosrajpwqoh74/Time%20difference%20based%20on%20two%20key%20columns%20.pb...

 

1.png

 

Regards,

Cherie

 

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Posts: 1,343
Registered: ‎07-25-2018

Re: Time difference based on two key columns

Hi @Elirot

 

Get a better way for you. You can get it by 2 calculated columns:

Sort =
IF (
    LOOKUPVALUE ( Table2[Team], Table2[Index], Table2[Index] - 1 )
        = Table2[Team],
    0,
    1
)
Column =
SUMX (
    FILTER ( Table2, Table2[Index] <= EARLIER ( Table2[Index] ) ),
    Table2[Sort]
)

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 15
Registered: ‎06-20-2018

Re: Time difference based on two key columns

Hi Cherie,

For the last week I was working on the solution you've suggested.

Using your firts way to create the group I came out with odd results, i.e. I've got the same Group for different AccountAppId/Team.

Using your second method, trying to create the second column (Column =
SUMX (
FILTER ( Table2, Table2[Index] <= EARLIER ( Table2[Index] ) ),
Table2[Sort]
),

the creation of it doesn't stop, i.e. it is already runs for days...

So, I'm not sure what's wrong with it, but each table has around 1.6M records.

 

Cheers

 

Eli

Data View.jpg

Community Support Team
Posts: 1,343
Registered: ‎07-25-2018

Re: Time difference based on two key columns

Hi @Elirot

 

It seems a measure is better than calculated column for your data. Here is the file for your reference.

Group2 =
SUMX (
    FILTER ( ALL ( table2 ), Table2[Index] <= MAX ( Table2[Index] ) ),
    Table2[Sort]
)

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 15
Registered: ‎06-20-2018

Re: Time difference based on two key columns

Hi Cherie,

 

Well, I've tried with the measure as well, but unfortunately got an error message - lack of resources (see image), even if I filter it down only to the current year.

 

Thanks

 

Eli

 

Error.jpg