cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Foxxon28
Helper I
Helper I

Looking back and in the future in DAX code

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.
1 ACCEPTED SOLUTION
MFelix
Super User III
Super User III

Hi @Foxxon28 ,

 

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 )

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
v-caitlyn-mstf
Community Support
Community Support

Hi@Foxxon28

 

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
Super User III
Super User III

Hi @Foxxon28 ,

 

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 )

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.