Forum Discussion

Elirot's avatar
Elirot
Frequent Visitor
7 years ago

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

 

Need Help

11 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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's avatar
      Elirot
      Frequent Visitor
      7 years ago

      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