Time difference based on two key columns

[Elirot]

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

[v-cherch-msft]

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

[Elirot]

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

[v-cherch-msft]

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

[Elirot]

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

[v-cherch-msft]

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...

Regards,

Cherie

[v-cherch-msft]

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]
)

Regards,

Cherie

[Elirot]

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

[v-cherch-msft]

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

[Elirot]

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

 

[v-cherch-msft]

Hi @Elirot

I've forgot your data is large. I would appreciated it if you could share your sample file which could reproduce your scenario.

Regards,

Cherie

[Elirot]

Hi Cherie,

Sorry it took me so long but business prioirities are always in place... 

Please find the link to the data file.

Regards,

Eli

Data file