Count of Houses Between Dates

[abarnett]

Hello! I am hoping this is simpler than it sounds, but I am looking to find a way to get the count of houses we have in WIP with a certain time period as in. I have a table with a house number, release date, and a close date. I want to know within a given month how many are in WIP. So as in example: November of 2018, I would want to know the count of houses with a close date after November of 2018 (or none at all) but a release date of November of 2018 or before. 

I want the criteria to change for whatever month I choose. 

Thank you!

[abarnett]

Here is some code I have so far:

# of Houses in WIP = CALCULATE(COUNTROWS(HOUSEMASTER),FILTER(HOUSEMASTER,(HOUSEMASTER[RELEASE_DATE] <= LASTDATE('Date Table'[Date]) && HOUSEMASTER[SETTLEMENT_DATE]>= FIRSTDATE('Date Table'[Date]))))

The results miss some houses and doesn't take into account if there is a blank settlement date which is fairly important. Not sure how to add that. 

[v-shex-msft]

HI @abarnett ,

Please try to use below measure formula if it works:

# of Houses in WIP =
CALCULATE (
    COUNTROWS ( HOUSEMASTER ),
    FILTER (
        ALLSELECTED ( HOUSEMASTER ),
        (
            HOUSEMASTER[RELEASE_DATE] <= MIN ( 'Date Table'[Date] )
                && HOUSEMASTER[SETTLEMENT_DATE] >= MAX ( 'Date Table'[Date] )
        )
    )
)

If above not help, please share some sample data for test.

Regards,

Xiaoxin Sheng

[abarnett · Accepted solution]

# of Houses in WIP = CALCULATE(
                         COUNTROWS(HOUSEMASTER),
                         FILTER(
                             HOUSEMASTER,(AND(HOUSEMASTER[RELEASE_DATE] <= LASTDATE('Date Table'[Date]),HOUSEMASTER[RELEASE_DATE]) && OR(HOUSEMASTER[SETTLEMENT_DATE]>= FIRSTDATE('Date Table'[Date]),HOUSEMASTER[SETTLEMENT_DATE]=0)
                          )
                         )
                      )

This is the code I ended up using and does show what I am looking for. Thank you for your help though!