Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
lozg
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 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 
1 ACCEPTED SOLUTION
daXtreme
Solution Sage
Solution Sage

[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

View solution in original post

4 REPLIES 4
daXtreme
Solution Sage
Solution Sage

[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

Thank you very much for that, daX!

amitchandak
Super User
Super User

@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...

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.