Using date and ID column to compare rows from two tables

[Anonymous]

Hi All,

I'm looking to create a measure that can tell me how many contacts that I did not meet with in the 4-8 weeks after I requested a meeting. I'm strugging and can't seem to get the right combination of FILTER functions (of which, I'm a novice).

I have 3 tables: 1) Meeting Requests, 2) Meetings and 3) Contacts:

The desired outcome in the above scenario is 1 (red). I reached out on 2/1/2018 and didn't meet in the next 4-8weeks.

Ultimately I'd like to be able to slice this measure in visuals by year. Potentially other slices as well.

Any ideas?

Appreciate the help.

Thank you.

Shaun

[v-chuncz-msft]

@Anonymous,

Just like CONTAINS, you may try using COUNTROWS, FILTER and DATEDIFF in a measure.

[cpmohanraj]

My suggested approach,

 - Add a date column in the Meeting Requests that will represent 4 - 8 weeks boundary from the request date

 - The above step creates a date range for every meeting request and all that we need to do is to check if there was any meeting with the respective person held between this date range

You may know a better way of doing this date range join. I came across a post that explains how to use DAX table function to create a date range join - Join Data on Date Range

Hope this helps.