Forum Discussion

tagban's avatar
tagban
Icon for Helper I rankHelper I
2 years ago
Solved

People active during Date Range

I've gone thru a few dozen posts that seemingly asked t he same question but the answers never quite worked for me so I'm going to post this in hopes it helps me and others. We have members that join...
Need Help
  • tagban's avatar
    tagban
    2 years ago
    Number of Active People = 
VAR _selDateMin = MIN('Date'[Date])
VAR _selDateMax = MAX('Date'[Date])
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[id] ),
        FILTER (
            'Table',
            'Table'[enrollment_start] <= _selDateMax
                && (
                    'Table'[enrollment_end] >= _selDateMin
                        || ISBLANK ( 'Table'[enrollment_end] )
                )
        )
    )

    This was the fix for this. It allows me to filter anyone that was active at some time during this time. This was largely thanks to you but this was the actual solution. I just needed to account for the MIN and MAX. 

    Thank you for all of your help Anonymous 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi tagban ,

Base on your description, it seems like you want to get the number of active people during the selected date period. You can create a measure as below to get it, please find the details in the attachment.

Number of Active People = 
VAR _seldate =
SELECTEDVALUE('Date'[Date])
RETURN
   SUMX (
        'Table',
        VAR peopleStartDate = [enrollment_start]
        VAR peopleEndDate = [enrollment_end]
        RETURN
            IF (
                peopleStartDate <= _seldate
                    && OR ( peopleEndDate >= _seldate, peopleEndDate = BLANK () ),
                1,
                0
            )
    )

In additional, you can refer the following links to get it:

Power BI: Employee count by month tutorial - Finance BI (finance-bi.com)

Employee Count =
VAR selectedDate =
    MAX ( 'Date'[Date] )
RETURN
    SUMX (
        'Employees',
        VAR employeeStartDate = [Start Date]
        VAR employeeEndDate = [End Date]
        RETURN
            IF (
                employeeStartDate <= selectedDate
                    && OR ( employeeEndDate >= selectedDate, employeeEndDate = BLANK () ),
                1,
                0
            )
    )

How Many Staff Do We Currently Have – Multiple Dates Logic In Power BI Using DAX 

powerbi - Calculating the number of active employees as at previous months with DAX 

ActiveEmps :=
VAR ThisDate =
    MIN( Dim_Date[Date] )
RETURN
    CALCULATE(
        COUNTROWS( employee ),
        FILTER(
            ALL( employee ),
            employee[Start Date] < ThisDate
                && employee[Termination Date] > ThisDate
        )
    )

Best Regards