Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
abarnett
Helper I
Helper I

Count of Houses Between Dates

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!

1 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! 

View solution in original post

3 REPLIES 3
abarnett
Helper I
Helper I

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. 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

# 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! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.