Calculate value between two dates if date falls between two dates in another table

[mahra-in]

Hi

I need your help in calculating the sum of values if the date of that table falls in between two dates from another table

Table 1: Spend

Supplier	Category	Value	PO Date
ABB	Motors	2000	6-May-17
Siemens	Drives	1500	1-Apr-17
GE	Cable	1300	15-Dec-17
ABB	Motors	1000	18-Aug-17
Siemens	Cable	1200	15-Nov-17

Table 2: Contract

Supplier	Category	Effective Date	End Date
ABB	Motors	1/1/2017	30-Jun-17
Siemens	Drives	1/1/2017	1-Oct-17
GE	Cable	1/1/2017	31-Oct-17

Now I want result as 

ABB	2000
Siemens	1500

Since ABB has 2 PO's but only 1 PO falls between the dates from Table 2 similarly Siemens has 2 PO's but only 1 falls between Table 2 dates , GE has only 1 PO and it doesnot falls between the date.

Also the remaining supplier TECO doesnot covered under any contract hence not considered

Please help

[ChandeepChhabra · Accepted solution]

Hi, You can download the solution excel (powerpivot)

A few things that I have done

1. I have created a relationship between Client Name between Spend and Contract Table, assuming that the client name will not be duplicated in the Contract Table
2. Then I have written the following measure

Spend Between Dates = CALCULATE(SUM(Spend[Value]),FILTER(Spend,Spend[PO Date]>=RELATED(Contract[Effective Date])&&Spend[PO Date]<=RELATED(Contract[End Date])))

Snapshot of the result

[Anonymous]

Hi! Could you tell me please what should I do if between theese two tables is another one? This table keep all information about client account. Relationships is created by "account id".

[Anushka]

Hi,

You need to have a relationship beween the two tables to achieve this. (Better if you have numeric column rather than creating relationship between supplier columns.)

Then you can create a calculated column in Spend table , something like below.

ValueSpent= IF([PO Date] > RELATED(Contract[EffectiveDate].[Date]) && [Date] < RELATED(Contract[EndDate]), Spend[Value] , 0) 

Finally, get the sum of the newly created column. 

Hope this helps

Thanks