Forum Discussion

MStark's avatar
MStark
Icon for Helper III rankHelper III
3 years ago
Solved

Average Active EEs for Date Range

Hi,   I have a tableof employees with Hire, Termination and Rehire Dates. I also have a date table Im trying to get the average amount of active employees for whatever date range is selected on th...
Need Help
  • MStark's avatar
    MStark
    3 years ago

    I created the measure below and it works for now! Thanks so much for all your help!!

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi MStark ,

You can create a measure as below to get it, please find the details in the attachment.

Active Employee = 
VAR _seldate =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Employee'[Full Name] ),
        FILTER (
            'Employee',
            (
                'Employee'[Hire Date] <= _seldate
                    && (
                        ISBLANK ( 'Employee'[Termination Date] )
                            || 'Employee'[Termination Date] > _seldate
                    )
            )
                || ( 'Employee'[Rehire Date] <= _seldate
                && 'Employee'[Employee Status] = "Active" )
        )
    )

Best Regards

MStark's avatar
MStark
Icon for Helper III rankHelper III
3 years ago

Thanks for taking the time to look into this! I see it works in the test file you attached but when I copied the formula to the BI with real data, Im getting the same number for all months. Is there anything that needs to be updated?

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi MStark ,

    Please try to update the formula of measure [Active Employee] as below and check if it can return the correct result...

    Active Employee = 
VAR _selyear =
    SELECTEDVALUE ( 'Calendar'[Date].[Year] )
VAR _selmonth =
    SELECTEDVALUE ( 'Calendar'[Date].[MonthNo] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'EE Info'[Full Name] ),
        FILTER (
            'EE Info',
            (
                VALUE (
                    YEAR ( 'EE Info'[Hire Date] )
                        & IF (
                            MONTH ( 'EE Info'[Hire Date] ) < 10,
                            "0" & MONTH ( 'EE Info'[Hire Date] ),
                            MONTH ( 'EE Info'[Hire Date] )
                        )
                )
                    <= VALUE ( _selyear & IF ( _selmonth < 10, "0" & _selmonth, _selmonth ) )
                    && (
                        ISBLANK ( 'EE Info'[Termination Date] )
                            || VALUE (
                                YEAR ( 'EE Info'[Termination Date] )
                                    & IF (
                                        MONTH ( 'EE Info'[Termination Date] ) < 10,
                                        "0" & MONTH ( 'EE Info'[Termination Date] ),
                                        MONTH ( 'EE Info'[Termination Date] )
                                    )
                            )
                                > VALUE ( _selyear & IF ( _selmonth < 10, "0" & _selmonth, _selmonth ) )
                    )
            )
                || (
                    VALUE (
                        YEAR ( 'EE Info'[Rehire Date] )
                            & IF (
                                MONTH ( 'EE Info'[Rehire Date] ) < 10,
                                "0" & MONTH ( 'EE Info'[Rehire Date] ),
                                MONTH ( 'EE Info'[Rehire Date] )
                            )
                    )
                        <= VALUE ( _selyear & IF ( _selmonth < 10, "0" & _selmonth, _selmonth ) )
                        && 'EE Info'[Employee Status] = "Active"
                )
        )
    )

    Best Regards

    • MStark's avatar
      MStark
      Icon for Helper III rankHelper III
      3 years ago

      Still doesnt seem to work... does a relationship between the calendar and EE info table need to be active? I played around with measure Greg_Deckler provided and its working for now

      Thanks so much for your time and assistance!