Forum Discussion

AG_55's avatar
AG_55
Frequent Visitor
4 years ago
Solved

COUNTROWS with condition

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 dateCommentsStatusHire date
29.01.2021cancelHold 
21.12.2020FedOffer accepted14.01.2021
30.12.2020GriOffer accepted13.01.2021
24.01.2021DemOffer accepted12.01.2021
27.01.2021ShevOffer accepted21.01.2021
26.01.2021PolOffer accepted26.01.2021
28.01.2021SelOffer accepted23.01.2021
28.01.2021ElaOffer accepted26.01.2021
29.01.2021PilOffer accepted01.02.2021
29.01.2021SilOffer accepted07.03.2021
29.01.2021RomOffer accepted27.01.2021
29.01.2021SorOffer accepted28.01.2021
10.02.2021SimOffer accepted25.03.2021
17.02.2021SidOffer accepted24.02.2021
09.02.2021GavOffer accepted28.01.2021
01.02.2021GarOffer accepted05.02.2021
01.02.2021FedoOffer accepted25.01.2021
09.02.2021DruOffer accepted07.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 

Need Help
Tips and Tricks
  • AG_55's avatar
    AG_55
    4 years ago

    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.

     

     

1 Reply

  • AG_55's avatar
    AG_55
    Frequent Visitor
    4 years ago

    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.