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.
Hi,
Share some data and show the expected result.
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:
Table 2 of campaigns:
You can see in the example a campaign change, but other things could happen that should be considered, 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!
Hi,
Share the 2 tables in a format that can be pasted in an MS Excel file.
Here.
Table 1 of occurrences:Line Date Product Duration 11 04/03/2022 17:05 4945 5 11 04/03/2022 18:30 4945 2 12 04/03/2022 18:30 567244 3 11 04/03/2022 19:00 4945 5 13 04/03/2022 19:10 532277 10 11 05/03/2022 06:02 5051 7 13 05/03/2022 06:05 532277 2 Table 2 of campaigns:
Line Product Start date End Date Sum of duration 11 4945 24/02/2022 05/03/2022 12 567244 26/02/2022 15/03/2022 13 532277 26/02/2022 06/03/2022 11 5051 05/03/2022 07/03/2022 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.