How do I find Open Cases? (Active Employees per month?)

[amerianou]

Hello guys,

what's the right measure to calculate active employees per month in a dataset like this?

Not everyone is employed yet (blank dates).

Thanks in advance!!

[v-rzhou-msft · Accepted solution]

Hi @amerianou ,

I think you can try this code to create a measure.

Active employees =
VAR _MaxDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        (
            'Table'[Contract start date] <= _MaxDate
                && NOT ( ISBLANK ( 'Table'[Contract start date] ) )
        )
            && (
                'Table'[Contract end date] >= _MaxDate
                    || ISBLANK ( 'Table'[Contract end date] )
            )
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

You'd need a date table to put into a chart or table visual, not connected to the table you already have. Then you could create a measure like

Active employees =
var maxDate = MAX('Date'[Date])
var minDate = MIN('Date'[Date])
return CALCULATE( COUNTROWS('Table'),
'Table'[Start date] <= minDate && 
( 'Table'[End date] >= maxDate || ISBLANK('Table'[End date]) )
)