Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
User | Count |
---|---|
89 | |
88 | |
75 | |
67 | |
58 |
User | Count |
---|---|
137 | |
110 | |
93 | |
84 | |
69 |