Forum Discussion
Calculate value between two dates if date falls between two dates in another table
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
Hi, You can download the solution excel (powerpivot)
A few things that I have done
- 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
- 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
3 Replies
Hi, You can download the solution excel (powerpivot)
A few things that I have done
- 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
- 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
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
