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(...
  • daXtreme's avatar
    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