How to sum the hours if the date and ID is same?

[PBI_newuser]

Hi, i have a set of data as shown below.

How to calculate if the same date exist in the next line for same ID, then sum it, if not just take the hours in Col C.

[ERD · Accepted solution]

@PBI_newuser ,

If you are looking for a calculated column, you might try this code:

HoursCombiner = 
VAR currentIndex = T[Index]
VAR currentID = T[ID]
VAR nextDate = MINX(FILTER(T, T[Index] > currentIndex && T[ID] = currentID), T[Date])
RETURN
IF(T[Date] = nextDate, 
    SUMX(FILTER(T, T[Date] <= nextDate && T[ID] = currentID && T[Index] >= currentIndex), T[Hours]),
    T[Hours])

[amitchandak]

@PBI_newuser , Try new column like

sumx(filter(Table, [ID] =earlier([ID]) && [Date] =earlier([Date]) ),[hours])

[PBI_newuser]

Hi @amitchandak , by using the column you have shared, the total hours for B-123 on 20/12/2019 is 35.0.

However, I would like to combine once only if the ID and date is the same, and add the second line (larger) value. For example, the HoursCombiner for B-123 on 20/12/2019 should be 8.0+9.5+9.5 = 27.0.

[amitchandak]

@PBI_newuser , You have to add an index column in the power query and try a new column - https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

sumx(filter(Table, [ID] =earlier([ID]) && [Date] =earlier([Date]) && [Index] >earlier([index]) && [Hours] > earlier([hours]) ),[hours])

[PBI_newuser]

Hi @amitchandak , I couldn't get the output I want.

Here is the sample. Thanks for your help! 

[amitchandak]

@PBI_newuser , Only need one small change to the formula 

HoursCombiner = 'Table'[Hours] + sumx(filter('Table', 'Table'[ID] =earlier('Table'[ID]) && [Date] =earlier([Date]) && 'Table'[Index] >earlier('Table'[Index]) && 'Table'[Hours] > earlier('Table'[Hours]) ),'Table'[Hours])