Sum of values between two dates in same column

[TE]

Hi!

I have two tables: one that contains payments and another that contains the start date of a contract. These two tables are linked by a dates table and a table containing unique company names. 

I would like to calculate the sum of payments for each company on or after the start date of a contract and before the next start date of the companies new contract. Heres a excel representation of my tables. 

Thanks in advance!

-TE

[TE]

I have two tables: one that contains payments and another that contains the start date of a contract. These two tables are linked by a dates table and a table containing unique company names. 

I would like to calculate the sum of payments for each company on or after the start date of a contract and before the next start date of the companies new contract. Heres a excel representation of my tables. 

Thanks in advance!

-TE

[v-juanli-msft · Accepted solution]

Hi @TE

Look at my test

First, manage relationships among these tables

Second, in "company"table, create calculated columns

min = LOOKUPVALUE(Sheet1[min],Sheet1[company],[company])

max = LOOKUPVALUE(Sheet1[max],Sheet1[company],[company])

Third, in Sheet2,create calculated columns

min = RELATED(company[min])

max = RELATED(company[max])

flag = IF([max]=[min],1,0)

max2 = IF([flag]=1,CALCULATE(MAX([payment date]),ALLEXCEPT(Sheet2,Sheet2[company]))+1,[max])

sum = CALCULATE(SUM(Sheet2[payment]),FILTER(ALLEXCEPT(Sheet2,Sheet2[company]),[payment date]>=[min]&&[payment date]<[max2]))

Best Regards

Maggie