cancel
Showing results for
Did you mean:
Regular Visitor

## Count rows before date with a condition

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
1 ACCEPTED SOLUTION
Super User
``````[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``````
4 REPLIES 4
Super User
``````[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``````
Regular Visitor

Thank you very much for that, daX!

Super User
Regular Visitor

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