Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi, You can download the solution excel (powerpivot)
A few things that I have done
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 can download the solution excel (powerpivot)
A few things that I have done
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! 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".
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |