Forum Discussion

lozg's avatar
lozg
Regular Visitor
3 years ago
Solved

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 IDEmployee IDNameHire DateTermination DateEmployee StatusCompanyCompany CodeHome Department DescriptionTurnover Reason
11Example Employee 114/12/2021 ActiveCompany AA11Sales 
22Example Employee 223/04/2021 ActiveCompany AA11Marketing 
33Example Employee 315/07/2021 ActiveCompany AA11Finance 
44Example Employee 404/11/202123/05/2022TerminatedCompany AA11HRVoluntary
55Example Employee 516/11/2021 ActiveCompany AA11Legal 
66Example Employee 617/11/2021 ActiveCompany AA11Sales 
77Example Employee 723/04/202114/06/2022TerminatedCompany AA11MarketingVoluntary
88Example Employee 802/08/2021 ActiveCompany BB18Finance 
99Example Employee 901/09/2021 ActiveCompany BB18HR 
1010Example Employee 1022/09/2021 ActiveCompany BB18Legal 
COUNTROWS
datesbetween
DAX
  • daXtreme's avatar
    daXtreme
    3 years ago
    [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