Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter Rows based on dates in 2 tables

Hey all!   Firstly, a sample of my 2 tables.   Table1: Project                                                                        CustomerID    |   ProjectID     | ProjectDateCreated        ...
Need Help
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    5 years ago

    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)

amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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?