COUNTROWS with condition

[AG_55]

Hi everyone!

The task is to build a plan for recruiting group based on the entries in table. They open a vacancy by adding a new row and set estimated hire date under internal SLA and fill hire date when they close it. Hire date column remains empty till they sign a new employee.

Data sample:

Estimated hire date	Comments	Status	Hire date
29.01.2021	cancel	Hold
21.12.2020	Fed	Offer accepted	14.01.2021
30.12.2020	Gri	Offer accepted	13.01.2021
24.01.2021	Dem	Offer accepted	12.01.2021
27.01.2021	Shev	Offer accepted	21.01.2021
26.01.2021	Pol	Offer accepted	26.01.2021
28.01.2021	Sel	Offer accepted	23.01.2021
28.01.2021	Ela	Offer accepted	26.01.2021
29.01.2021	Pil	Offer accepted	01.02.2021
29.01.2021	Sil	Offer accepted	07.03.2021
29.01.2021	Rom	Offer accepted	27.01.2021
29.01.2021	Sor	Offer accepted	28.01.2021
10.02.2021	Sim	Offer accepted	25.03.2021
17.02.2021	Sid	Offer accepted	24.02.2021
09.02.2021	Gav	Offer accepted	28.01.2021
01.02.2021	Gar	Offer accepted	05.02.2021
01.02.2021	Fedo	Offer accepted	25.01.2021
09.02.2021	Dru	Offer accepted	07.02.2021

2 hard points for me here:

1. If the vacancy is or was not closed within the month of estimated hire date it should go for the next month.
2. Overdue vacancies should be calculated considering this condition.

So I'm asking for help to build 2 measures for this total plan and counting overdue vacancies for each month.

All I have now is running total for rows:

 = CALCULATE(COUNTROWS('Regions'),
    FILTER(ALL('Calendar'),
    'Calendar'[Date]<=MAX('Calendar'[Date])
    ))

I tried to add additional context for someting like 'Regions'[Estimated hire date] < 'Regions'[Hire date] but not succeeded.

Would be glad for your comments.
PBIX file with sample data: https://www.mediafire.com/file/q9lgrjp3eyb1t55/tst2.pbix/file 

[AG_55 · Accepted solution]

Got a solution. At first, we need to set 2 inactive relationships in model:

• 'Calendar'[Date] -> 'Regions'[Estimated hire date]
• 'Calendar'[Date] -> 'Regions'[Hire date]

And then we have the measure:

RT = 
CALCULATE( COUNTROWS('Regions'),
    FILTER( VALUES('Regions'[Estimated hire date]), 'Regions'[Estimated hire date] <= MAX( 'Calendar'[Date] ) ),
    FILTER( VALUES('Regions'[Hire date]),
        OR('Regions'[Hire date] >= MIN( 'Calendar'[Date] ),
           'Regions'[Hire date] = BLANK() ) )
)

'Calendar'[Date] should be put in visualization to build dates context:

Thus we already have 3 rows for March, they are for vacancies that were not closed but opened in Dec, Jan or Feb.