Need help writing calculate formula using dates between two tables

[condo]

I am trying to write a formula in the date table to calculate the number of ID's where the Date (on the date table) is between two dates on the ID table. See below for example: (Trying to create formula for highlighted column)

What I am trying to write a formula to tell me: DISTINCTCOUNT OF ID's if DATE (date table) is >=[Date Opened] AND DATE (Date table) is <=[Date Closed].

Any help would be greatly appreciated!

[jdbuchanan71 · Accepted solution]

Hello @condo 

I think this will work for you.  It is a calculated column on the Date table.

Count of IDS = 
CALCULATE(
    DISTINCTCOUNT( 'ID Table'[ID] ),
    FILTER ( 
        'ID Table',
        'ID Table'[Date Opened] <= 'Date Table'[Date] && 
        'ID Table'[Date Closed] >= 'Date Table'[Date] )
    )

[condo]

That did the trick- thanks!