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

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's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

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

      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's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        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