Summarize time data into new table

[shellyvanerp]

Hi, 

I have a table with car trips, looking like this: 

       StartDate                      StAddress   EndDate                      EndAddress  Distance  Duration  SameTrip 

1     15/1/2018 06:56:07      street 1       15/1/2018 07:00:00     street 2         1,5            3,88                

2     15/1/2018 07:01:57      street 2       15/1/2018 07:02:57     street 3         1               1              yes 

3     15/1/2018 07:03:57      street 3       15/1/2018 07:58:22     street 4         56,9          54,42        yes 

4     15/1/2018 17:19:51      street 4        15/1/2018 18:32:31     street 1        57            72,63

I would like to summarize this data into a new table looking like this (most important to sum distance and duration, end address is not the most important part but nice if it works): 

    StartDate                      StAddress   EndDate                      EndAddress  Distance   DurationTrip  SameTrip 

1   15/1/2018 06:56:07      street 1       15/1/2018 07:58:22     street 4          59,4          59,3                

4   15/1/2018 17:19:51      street 4       15/1/2018 18:32:31     street 1          57             72,63

I am trying to get the indexes of the rows that have SameTrip = "yes" and the same date to be the equal, but it seems hard when a trip has more than 2 rows. 

Does anyone have an idea to fix this in DAX code? (some of the columns of this dataset are not available in the power query so it has to be in DAX) 

Shelly 

[shellyvanerp]

I just now found a way to get a new index column that gives the same index to rows that belong to the same trip. But it is not pretty, so if anyone has a better solution I am very happy to hear. 

NewIndex = SWITCH(

TRUE();

-- window 1

CapBusinessTrips[SameTrip] <> BLANK() && CALCULATE(MAX(CapBusinessTrips[SameTrip]);FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-1)) <> BLANK() && CALCULATE(MAX(CapBusinessTrips[SameTrip]); FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-2)) <> BLANK() && CALCULATE(MAX(CapBusinessTrips[SameTrip]);FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-3)) = BLANK(); CALCULATE(MAX(CapBusinessTrips[Index]);FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-3));

-- window 2

CapBusinessTrips[SameTrip] <> BLANK() && CALCULATE(MAX(CapBusinessTrips[SameTrip]);FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-1)) <> BLANK() && CALCULATE(MAX(CapBusinessTrips[SameTrip]);FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-2)) = BLANK(); CALCULATE(MAX(CapBusinessTrips[Index]);FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-2));

-- window 3

CapBusinessTrips[SameTrip] <> BLANK() && CALCULATE(MAX(CapBusinessTrips[SameTrip]);FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-1)) = BLANK(); CALCULATE(MAX(CapBusinessTrips[Index]);FILTER(CapBusinessTrips; CapBusinessTrips[Index]=EARLIER(CapBusinessTrips[Index])-1));

-- else

CapBusinessTrips[Index])