lookup if record exists in date range

[aking11]

hey all - new to powerbi and trying to build a report. I feel like it should be simple to do, but am stumped! 

I have two tables with an active relationship. A list of employees in one table, then a list of meeting notes in another (employee ID is the relationship). One meeting has to occur within each quarter (Jan-Mar, Apr-Jun, etc). I want to know if a meeting has taken place within each quarter... how would i go about finding this information? I would like to be able to add the new measure (or whatever is best) to a table visual to show if somethign has or has not happened this qtr, etc. 

Thanks for the help!

[v-jialongy-msft · Accepted solution]

@lbendlin  @PhilipTreacy  Thanks for your prompt reply!

I tried to create a sample data myself based on the user's requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution: 

Hi @aking11 

Based on your needs, I have created the following table.

You can use the following Dax to find out if there are meetings for the quarter.

Q1 2024 = 
IF (
    SELECTEDVALUE ( 'Table'[Employee ID] )
        = SELECTEDVALUE ( 'Table (2)'[Employee ID] ),
    IF (
        MONTH ( SELECTEDVALUE ( 'Table (2)'[Meeting Date] ) ) <= 3
            && MONTH ( SELECTEDVALUE ( 'Table (2)'[Meeting Date] ) ) >= 1,
        "yes",
        "no"
    )
)

Q2 2024 = 
IF (
    SELECTEDVALUE ( 'Table'[Employee ID] )
        = SELECTEDVALUE ( 'Table (2)'[Employee ID] ),
    IF (
        MONTH ( SELECTEDVALUE ( 'Table (2)'[Meeting Date] ) ) <= 6
            && MONTH ( SELECTEDVALUE ( 'Table (2)'[Meeting Date] ) ) >= 4,
        "yes",
        "no"
    )
)

Q3 2024 = 
IF (
    SELECTEDVALUE ( 'Table'[Employee ID] )
        = SELECTEDVALUE ( 'Table (2)'[Employee ID] ),
    IF (
        MONTH ( SELECTEDVALUE ( 'Table (2)'[Meeting Date] ) ) <= 9
            && MONTH ( SELECTEDVALUE ( 'Table (2)'[Meeting Date] ) ) >= 7,
        "yes",
        "no"
    )
)

Q4 2024 = 
IF (
    SELECTEDVALUE ( 'Table'[Employee ID] )
        = SELECTEDVALUE ( 'Table (2)'[Employee ID] ),
    IF (
        MONTH ( SELECTEDVALUE ( 'Table (2)'[Meeting Date] ) ) <= 12
            && MONTH ( SELECTEDVALUE ( 'Table (2)'[Meeting Date] ) ) >= 10,
        "yes",
        "no"
    )
)

Best Regards,

Jayleny

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

[lbendlin]

Use a (disconnected) calendar table that has dates and quarters. Then in your measure verify if the dates in the quarter INTERSECT with the meetings.  A zero result means no meetings were held in that quarter.

[PhilipTreacy]

Hi @aking11 

Not enough info.  What is the Employee ID for? Yuo said you want to know if a meeting has taken place each quarter.  What exactly are the criteria?  Any meeting?  Or is it related to each Employee?

What exactly do you want to see as a result - please supply an example.

Also please supply some data otherwise we have to recreate everything and guess what data you have. 

Regards

Phil

[aking11]

Hey Phil - here is some more info:

There is an employee ID associated with each employee. The ID is in both tables. One table is just demographic information of the employees, names, locations, job, etc. The other table houses records of meetings with their mentor. We have an expectation that there is a mentor meeting once per quarter. The mentors log their meetings in this table that includes the employee ID and notes about the meeting (including date). There is only a record if the meeting took place. Something like the below:

Employee ID	First Name	Last Name
1234	Joe	Smith

Employee ID	Mentor	Meeting Date	Meeting Type
1234	John	4/11/2024	Phone Call

The output I am looking for is to be able to see if there was a meeting/contact made during each quarter. So add a column to a table that includes YES/NO, etc for each Quarter. Hope that makes sense. 

Employee ID	First Name	Last Name	Q1 2024	Q2 2024
1234	Joe	Smith	NO	YES