Forum Discussion

ojferreira's avatar
ojferreira
Frequent Visitor
4 years ago
Solved

Calculate and filter using multiple tables

Hello,   I have two data tables, one (I'll call it table 1) for occurrences by date, product, production line, and how long it lasted. And another one with the campaign with start and end date, lin...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    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.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

Hi,

Share some data and show the expected result. 

ojferreira's avatar
ojferreira
Frequent Visitor
4 years ago

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!