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(...
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
daXtreme's avatar
daXtreme
Solution Sage
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
lozg's avatar
lozg
Regular Visitor
3 years ago

Thank you very much for that, daX!