Historical Count of Open Positions By Month

[pikers]

Hi all,

I have what I thought would be an easy request turn into me ripping my hair out (or what's left of it) over the past two days, I could use some help.

The ask is to provide counts of open reqs (open positions) as far back as we have data. The model is as simple as it gets, just a table with req data and a date table. Within the req table there is ID, create_date, on_hold_date, off_hold_date, and closed_date. What I'm trying to do is get a count of open reqs by month for the past X years. For a req to be considered "open" for a given month, it needs to have:

• a create_date before the last day of the month
• a closed_date of NULL or a closed_date after the end of the month
• on hold date is:
  • Null
  • After the end of the month
  • Before the end of the month but off_hold_date is also before or equal to the last day of the month

I tried a few different measures and I couldn't get anything to work. Any help would be greatly appreciated. 

[johnt75]

Try

Open reqs =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Reqs' ),
        REMOVEFILTERS ( 'Date' ),
        'Reqs'[Open date] <= ReferenceDate,
        'Reqs'[Closed date] > ReferenceDate
            || ISBLANK ( 'Reqs'[Closed date] ),
        'Reqs'[On hold date] > ReferenceDate
            || ISBLANK ( 'Reqs'[On hold date] )
            || 'Reqs'[Off hold date] <= ReferenceDate
    )
RETURN
    Result