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.
I have a variety of products, 14 lines and the campaigns last only some days, therefore, I have many entries for campaigns, and some products repeat themselfs on the same line duing the year. Therefore, I needed something like I explained:
- Relate the occurence to a campaign using the variables -> line, product and start date. (Pulling data from table 2 into table 1)
or
- Relate the campaign to multiple occurrences and sum the duration value given in table 1, using the variables -> line, product and the occurrence date, that should be recognized as between start and end date of the campaign (Pulling data from table 1 to table 2)
I have line and product on both tables, on table 1 I have the occurence date and duration; and on table 2 I have the start and end of the campaign.
Don't know if I made my problem clear or If any other information could help.
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.
ORCreate 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