- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Time difference based on two key columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-02-2018 03:59 PM

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

## Re: Time difference based on two key columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-03-2018 10:12 PM

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

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*## Re: Time difference based on two key columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-04-2018 08:24 PM

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

## Re: Time difference based on two key columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-05-2018 06:42 PM

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

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*## Re: Time difference based on two key columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2018 03:37 PM

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

## Re: Time difference based on two key columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2018 06:20 PM

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

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*## Re: Time difference based on two key columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-13-2018 06:50 PM

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

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*## Re: Time difference based on two key columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-17-2018 08:15 PM

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

## Re: Time difference based on two key columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-18-2018 01:35 AM

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

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*## Re: Time difference based on two key columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-19-2018 09:05 PM

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