Filter Rows based on dates in 2 tables

[Anonymous]

Hey all!

Firstly, a sample of my 2 tables.

Table1: Project                                                                       

CustomerID    |   ProjectID     | ProjectDateCreated                 

1                             5                     2020-13-05                               

2                             5                     2020-13-05                                

3                             5                     2021-01-01                                

 Table2: Activities     

CustomerID    |   ActivityType     | ActivityDate                 

1                            Call                     2020-21-05                               

1                           Visit                     2020-05-05                                

2                            Mail                    2020-03-01   

2                            Call                     2021-10-01

3                            Visit                    2020-08-10

What I want to do is to: Check all the activities that have taken place, after a Project was planned on that customer. So in this example activities in red should be filtered, because the ActivityDate < ProjectDateCreated on CustomerID. It doesn't matter if the Project isn't related to the Activity. 

I've tried the following calculated column, to filter out the activities:

Relevance= IF(FILTER(Projects, Projects[CustomerID] = Activities[CustomerID] && Activities[ActivityDate] >= Project[ProjectDateCreated]), 1,0)

But this give me an error saying the expression is trying to refer to multiple columns.

Sidenote: The dates column is actually just a string of numbers "20210501", but for clarity sakes, I've shown it differently in my example

1) Could I filter these rows in the Power Query editor using M? If so, I'd love to hear it!

2) How do I need to change my DAX in order for it to work, in case point 1 isn't possible.

I'd love to hear it and thanks in advance,

Daniël 🙂

[Anonymous]

Hi @Anonymous ,

Try this measure

Relevance = 
IF (
    CALCULATE (
        MAX ( 'Activities'[ActivityDate] ),
        ALLEXCEPT ( Activities, Activities[CustomerID] )
    )
        < CALCULATE (
            MAX ( 'Project'[ProjectDateCreated] ),
            ALLEXCEPT ( 'Project', Project[CustomerID] )
        ),
    1,
    0
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous , create a new column like

Relevance=
var _1 = Countx(FILTER(Projects, Projects[CustomerID] = Activities[CustomerID] && Activities[ActivityDate] >= Project[ProjectDateCreated]),Projects[CustomerID])
return
if(isblank(_1),0,1)

[Anonymous]

Maybe yet another question regarding your answer.

Let's say the Activity can be longer than 30 days away from ProjectDateCreated and should also look 7 days in the past. How would I add that logic?

I've tested "+30" on numerous spots, but I am not sure.

So activities 7 days in the past, prior to the creation of the event and no more than 30 days after the creation of the event. I'd love to hear it :)!

Would je be willing to tell me?