Count active employers

[ElinG]

Hi I am trying to count active employers aggregated per month, I have a start date and a end date and a date 9999-12-31 if its still active or without end date. The start date can be greater than the actul day.

My code count employers that have a start date after this day 

Aktiva personer =

CALCULATE (

    DISTINCTCOUNT ( 'analytics employment_periods'[sk_employment] ),

    FILTER (

        'analytics employment_periods',

        'analytics employment_periods'[Period_start_datum] >= MIN ( 'Kalender'[Date] )

            && (

                'analytics employment_periods'[Period_slut_datum] <= MAX ( 'Kalender'[Date] )

                || 'analytics employment_periods'[Period_slut_datum] = DATE ( 9999, 12, 31 )

            )

    )

)

I need help becouse now my code says that when a person have a start date like 2031-01-01 the it should be counted. 

/ELIN 

[johnt75 · Accepted solution]

If you're trying to ignore people with a start date in the future you could add another clause to the FILTER

Aktiva personer =
CALCULATE (
    DISTINCTCOUNT ( 'analytics employment_periods'[sk_employment] ),
    FILTER (
        'analytics employment_periods',
        'analytics employment_periods'[Period_start_datum] >= MIN ( 'Kalender'[Date] )
            && 'analytics employment_periods'[Period_start_datum] <= TODAY ()
            && (
                'analytics employment_periods'[Period_slut_datum] <= MAX ( 'Kalender'[Date] )
                    || 'analytics employment_periods'[Period_slut_datum] = DATE ( 9999, 12, 31 )
            )
    )
)

[ElinG]

Tnx for a really good solution 

[ElinG]

Thanx but it stil the same problem here is a example of people that conut that shouldn't

They have a start date for example 2026-01 and so on

[v-saisrao-msft]

Hi @ElinG,

I reproduced the issue using the sample data and obtained the following output. I have attached the PBIX file for your reference.

Aktiva personer = 
CALCULATE(
    DISTINCTCOUNT('employment_periods'[sk_employment]),
    FILTER(
        'employment_periods',
        'employment_periods'[Period_start_datum] <= MAX('Kalender'[Date])
            && (
                'employment_periods'[Period_slut_datum] >= MIN('Kalender'[Date])
                || 'employment_periods'[Period_slut_datum] = DATE(9999,12,31)
            )
    )
)

Hope this helps

Thank you.

[MasonMA]

@ElinG 

Hi, your logic is employees are active if their start date is before or equal to the end of the period you’re looking at,

and their end date is either missing / 9999-12-31, or after the start of the period. so try this instead, 

CALCULATE(

    DISTINCTCOUNT('analytics employment_periods'[sk_employment]),

    FILTER(

        'analytics employment_periods',

        'analytics employment_periods'[Period_start_datum] <= MAX('Kalender'[Date]) &&

        (

            'analytics employment_periods'[Period_slut_datum] >= MIN('Kalender'[Date]) ||

            'analytics employment_periods'[Period_slut_datum] = DATE(9999, 12, 31)

        )

    )

)

[FBergamaschi]

I just suggest an optmization of @MasonMA great code

If the table you have is huge, the below can improve performance

CALCULATE(

    DISTINCTCOUNT('analytics employment_periods'[sk_employment]),

    FILTER(

           SUMMARIZE(

        'analytics employment_periods',

employment_periods'[Period_start_datum],employment_periods'[Period_slut_datum]),

        'analytics employment_periods'[Period_start_datum] <= MAX('Kalender'[Date]) &&

        (

            'analytics employment_periods'[Period_slut_datum] >= MIN('Kalender'[Date]) ||

            'analytics employment_periods'[Period_slut_datum] = DATE(9999, 12, 31)

        )

    )

)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI