If Statement Comparing Dates Between Tables

[Dawson16]

Hello, I am having trouble finding any information on a problem I'm having. I have a couple tables which are related by common ID numbers. The first table contains project information, which is higher-level, and the second contains work order information for each project, which is more granular. Ideally, all work order start and finish dates should fall within the start and finish date of the project's, and this is what I am trying to check. To make it a bit simpler, I've pasted a visual below to aid in my explanation: 

I was able to create calculated columns on the work order table to determine if each row was within the date range of its respective project. I cannot figure out how to create a column in the project table that looks through each of the projects' work orders and says whether all of the respective start dates and finish dates are within the range. Since there are multiple IDs in the work order table matching the project IDs, I get the error message "a table of multiple values was supplied where a single value was expected." Is there any way to make this calculation like I've shown in yellow? 

Thank you in advance for any help you can offer!

[v-yanjiang-msft · Accepted solution]

Hi @Dawson16 ,

According to your description, here's my solution.

The two tables are related with the ID# column. Then create two calculated columns in the Work Orders table.

Start Date in Project Range? =
IF (
    'Work Orders'[Start Date] >= RELATED ( 'Projects'[Start Date] )
        && 'Work Orders'[Start Date] <= RELATED ( Projects[Finish Date] ),
    "In Range",
    "Out of Range"
)

Finish Date in Project Range? =
IF (
    'Work Orders'[Finish Date] >= RELATED ( 'Projects'[Start Date] )
        && 'Work Orders'[Finish Date] <= RELATED ( Projects[Finish Date] ),
    "In Range",
    "Out of Range"
)

Create two calculated columns in the Projects table.

ALL WO Start Dates in Range? =
IF (
    COUNTROWS (
        FILTER (
            'Work Orders',
            'Work Orders'[ID #] = EARLIER ( 'Projects'[ID #] )
                && 'Work Orders'[Start Date in Project Range?] = "Out of Range"
        )
    ) > 0,
    "No",
    "Yes"
)

ALL WO Finish Dates in Range? =
IF (
    COUNTROWS (
        FILTER (
            'Work Orders',
            'Work Orders'[ID #] = EARLIER ( 'Projects'[ID #] )
                && 'Work Orders'[Finish Date in Project Range?] = "Out of Range"
        )
    ) > 0,
    "No",
    "Yes"
)

Get the correct result.

Best Regards,
Community Support Team _ kalyj

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

[Dawson16]

This is exactly what I was looking for and should work great. Thank you so much for taking the time to answer this!

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I cannot help you without meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523