Looking back and in the future in DAX code

Question

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.&nbsp;&nbsp;Original Post: (Firstly, a sample of my 2 tables.&nbsp;Table1: Project&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CustomerID&nbsp; &nbsp; |&nbsp; &nbsp;ProjectID&nbsp; &nbsp; &nbsp;| ProjectDateCreated&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2020-13-05&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2020-13-05&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2021-01-01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;Table2: Activities&nbsp; &nbsp; &nbsp;CustomerID&nbsp; &nbsp; |&nbsp; &nbsp;ActivityType&nbsp; &nbsp; &nbsp;| ActivityDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Call&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2020-21-05&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Visit&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2020-05-05&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Mail&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2020-03-01&nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Call&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2021-10-013&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Visit&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2020-08-10&nbsp;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&nbsp;red&nbsp;should be filtered, because the ActivityDate &lt; ProjectDateCreated on CustomerID. It doesn't matter if the Project isn't related to the Activity.&nbsp;I've tried the following calculated column, to filter out the activities:Relevance= IF(FILTER(Projects, Projects[CustomerID] = Activities[CustomerID] &amp;&amp; Activities[ActivityDate] &gt;= Project[ProjectDateCreated]), 1,0)&nbsp;But this give me an error saying the expression is trying to refer to multiple columns.Sidenote:&nbsp;The dates column is actually just a string of numbers "20210501", but for clarity sakes, I've shown it differently in my example )&nbsp;&nbsp;NEW:User Amitchandak helped me out with the following DAX code:Relevance=var _1 = Countx(FILTER(Projects, Projects[CustomerID] = Activities[CustomerID] &amp;&amp; Activities[ActivityDate] &gt;= Project[ProjectDateCreated]),Projects[CustomerID])returnif(isblank(_1),0,1)&nbsp;This code works great, but I need to add 2 things.1: It also has to look back no more than&nbsp;7 days2: the activities can be no longer than&nbsp;+30 days away from creation date.&nbsp;Ive tried adding -7 +30 in several parts of the DAX, but couldn't get it to work. What could I do best?&nbsp;Kind regards,Daniël&nbsp;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.

mfelix · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
Belive that this can be done by making the following:
Relevance =
VAR _1 =
    COUNTX (
        FILTER (
            Projects,
            Projects[CustomerID] = Activities[CustomerID]
                &amp;&amp; Activities[ActivityDate] &gt;= Project[ProjectDateCreated] - 7
                &amp;&amp; Activities[ActivityDate] &lt;= Project[ProjectDateCreated] + 30
        ),
        Projects[CustomerID]
    )
RETURN
    IF ( ISBLANK ( _1 ), 0, 1 )

Forum Discussion

Looking back and in the future in DAX code

2 Replies

Related content

Datamart future

Looking at future orders based on date selection

Min Max Tooltip & future months

Look back 365 days calculation

MySQL database mirroring future in Fabric