Forum Discussion

ElinG's avatar
ElinG
Frequent Visitor
11 months ago
Solved

Count active employers

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's avatar
    johnt75
    11 months ago

    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 )
            )
    )
)

9 Replies

  • MasonMA's avatar
    MasonMA
    Super User
    11 months ago

    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's avatar
      FBergamaschi
      Super User
      11 months ago

      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

  • ElinG's avatar
    ElinG
    Frequent Visitor
    11 months ago

    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's avatar
      v-saisrao-msft
      Community Support
      11 months ago

      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.

       

  • johnt75's avatar
    johnt75
    Super User
    11 months ago

    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's avatar
    ElinG
    Frequent Visitor
    10 months ago

    Hi agian! 

     

    My calculation of this is very slow I have only 2 years right now and more will it be is there any solutions thayt will move up the speed for the calculations, becouase right now I can only have years in the filter if I ad month there will be an error

     

    Anställd =
    CALCULATE(
        DISTINCTCOUNT('Anställda'[Individ_kundnummer]),
        FILTER(
            'Anställingsperiod',
            'Anställingsperiod'[Period_start_datum] <= MAX('Kalender (2024–idag)'[Date]) &&
            (
                'Anställingsperiod'[Period_slut_datum] >= MIN('Kalender (2024–idag)'[Date]) ||
                'Anställingsperiod'[Period_slut_datum] = DATE(9999, 12, 31)
            )
        )
    )