Looking back and in the future in DAX code

[Anonymous]

Hey all, this is a copy of a previous post I made. I already accepted it as the solution, but sadly I later realised I needed to add in more logic to the DAX. 

Original Post: (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 ) 

NEW:

User Amitchandak helped me out with the following DAX code:

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

This code works great, but I need to add 2 things.

1: It also has to look back no more than 7 days

2: the activities can be no longer than +30 days away from creation date.

Ive tried adding -7 +30 in several parts of the DAX, but couldn't get it to work. What could I do best?

Kind regards,

Daniël

PS: I've tried posting in my original post, but sadly haven't recieved a response yet, therefore I am creating a new post. Sorry if this is not inbetween the guidelines.

[v-xiaoyan-msft]

Hi@Anonymous ，

It's pleasant if your problem has been solved, could you please mark the reply as Answered? that way, other community members will easily find the solution when they get the same issue.

Hope it helps.

Best Regards,
Caitlyn Yan

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

[MFelix · Accepted solution]

Hi @Anonymous ,

Belive that this can be done by making the following:

Relevance =
VAR _1 =
    COUNTX (
        FILTER (
            Projects,
            Projects[CustomerID] = Activities[CustomerID]
                && Activities[ActivityDate] >= Project[ProjectDateCreated] - 7
                && Activities[ActivityDate] <= Project[ProjectDateCreated] + 30
        ),
        Projects[CustomerID]
    )
RETURN
    IF ( ISBLANK ( _1 ), 0, 1 )