Count rows before date with a condition

[lozg]

Hi there,
I'm having trouble adding another conditon to a DAX - to determine the number of voluntary leavers in a specified period.

For all leavers, the DAX I'm using is:

var mindate = calculate(min('Calendar'[Date]), ALL('Calendar'[Date]))

var maxdate = calculate(max('Calendar'[Date]), ALLSELECTED('Calendar'[Date]))

var cnt  =  CALCULATE(COUNTROWS(Master), DATESBETWEEN('Calendar'[Date], mindate, (maxdate+1)), USERELATIONSHIP('Calendar'[Date], Master[Termination Date]))

return cnt)

This works just fine.

However, I'm not sure how to get the calculation to work when adding in one more criteria, which would be:

Master[Turnover Reason] = "Voluntary"

In Excel, the equivalent formula is a simple COUNTIFS:
COUNTIFS(Master!E:E,"<"&$A$1,Master!J:J,"Voluntary") [where Column E is the termination date, A1 is the start date in the series and Column J is the termination reason]

Any ideas on how I can expand the DAX to accomodate counting the number of termination dates that fall in (or before) a date range where I also have a criteria that only returns those wirh Voluntary termination?

Below is a simple example of the raw data table

Position ID	Employee ID	Name	Hire Date	Termination Date	Employee Status	Company	Company Code	Home Department Description	Turnover Reason
1	1	Example Employee 1	14/12/2021		Active	Company A	A11	Sales
2	2	Example Employee 2	23/04/2021		Active	Company A	A11	Marketing
3	3	Example Employee 3	15/07/2021		Active	Company A	A11	Finance
4	4	Example Employee 4	04/11/2021	23/05/2022	Terminated	Company A	A11	HR	Voluntary
5	5	Example Employee 5	16/11/2021		Active	Company A	A11	Legal
6	6	Example Employee 6	17/11/2021		Active	Company A	A11	Sales
7	7	Example Employee 7	23/04/2021	14/06/2022	Terminated	Company A	A11	Marketing	Voluntary
8	8	Example Employee 8	02/08/2021		Active	Company B	B18	Finance
9	9	Example Employee 9	01/09/2021		Active	Company B	B18	HR
10	10	Example Employee 10	22/09/2021		Active	Company B	B18	Legal

[daXtreme · Accepted solution]

[Measure] = 
var mindate = 
    calculate(min(
        'Calendar'[Date]), 
        ALL('Calendar'[Date])
    )
var maxdate = 
    calculate(
        max('Calendar'[Date]), 
        ALLSELECTED('Calendar'[Date])
    )
var cnt  =  
    CALCULATE(
        COUNTROWS(Master), 
        DATESBETWEEN(
            'Calendar'[Date], 
            mindate, 
            maxdate + 1
        ),
        keepfilters(
            Master[Turnover Reason] = "voluntary" -- DAX is case-insensitive
        ),
        USERELATIONSHIP(
            'Calendar'[Date], 
            Master[Termination Date]
        )
    )
return
    cnt

[lozg]

Thank you very much for that, daX!

[amitchandak]

@lozg , Use the termination logic in HR blog or file with filter or Voluntary

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[lozg]

Thank you for the suggestion Amit!
I'll have a look over the blog to get some ideas on useful metrics.