Forum Discussion
Calculate and filter using multiple tables
This calculated column formula in the Campaigns table works
=CALCULATE(SUM(Occurences[Duration]),FILTER(Occurences,Occurences[Line]=EARLIER(Campaign[Line])&&Occurences[Product]=EARLIER(Campaign[Product])&&Occurences[Date]>=EARLIER(Campaign[Start date])&&Occurences[Date]<=EARLIER(Campaign[End Date])))Hope this helps.
Here are some examples of the tables, can't provide exactly the data, but these examples are similars.
I expect to insert the campaign code (some kind of identifier as the example bellow) in a new column on table 1, this way I can then add the durations to the campaigns. And even do more connections after.
OR
Create a new column on table 2 already adding the duration of occurrences from table 1 of that campaign.
Dates in the format - dd/mm/yy hh:mm
Table 1 of occurrences:
| Line | Date | Product | Duration (Min) | Campaign code |
| 11 | 4/3/22 17:05 | 4945 | 5 | "some kind of identifier" |
| 11 | 4/3/22 18:30 | 4945 | 2 | "ex: 11-4945-03/03/2022 06:00" |
| 12 | 4/3/22 18:30 | 567244 | 3 | ? |
| 11 | 4/3/22 19:00 | 4945 | 5 | ? |
| 13 | 4/3/22 19:10 | 532277 | 10 | ? |
| 11 | 5/3/22 6:02 | 5051 | 7 | ? |
| 13 | 5/3/22 6:05 | 532277 | 2 | ? |
Table 2 of campaigns:
| Line | Product | Start date | End date | Sum of duration |
| 11 | 4945 | 24/2/22 9:00 | 5/3/22 6:00 | ? |
| 12 | 567244 | 26/2/22 8:00 | 15/3/22 6:00 | ? |
| 13 | 532277 | 26/2/22 8:00 | 6/3/22 9:00 | ? |
| 11 | 5051 | 5/3/22 6:00 | 7/3/22 6:00 | ? |
You can see in the example a campaign change, but more complex things could happen, products being made ate different lines, even at the same time. This is why I think that a identifier with the combination of #LINE, #product and #startdate will be used...
I hope this make my problem clearer, it's my first time posting here.
Thanks for your time and help!