Count Total Workforce if Termination Date is >= to Date filter

[CJ_96601]

Good morning, 

Qlikview user here trying to move to Power BI.

Need to define a calculated measure to count active workforce based on selected year.

Name                                  Hire Date                            Termination date

Name 1                               January 2, 2010                  

Name 2                               Februray 5, 2010                  

Name 3                               April 2, 2011

Name 4                               April 15, 2012     

Name 5                              March 5, 2013

Name 6                              March 18, 2014                       January 16, 2017

Name 7                              Sept 15, 2015  

Name 8,                             January 7, 2016                      February 8, 2017

Name 9                              February 25, 2017  

Having above sample data.

If i choose year 2017, my active workforce is   8 as 1 resigned in Feb,

If i choose year 2016, my active worforce is 8 as Name 8 and Name 6 resigned in 2017        

Many thanks

[v-yulgu-msft · Accepted solution]

Hi @CJ_96601,

Suppose the data table is named as 'Workforce', also you have an extra table which lists unique year values named as 'Year Table'.

Please refer to below measure:

Count workforce =
CALCULATE (
    COUNT ( Workforce[Name] ),
    FILTER (
        Workforce,
        Workforce[Hire Date].[Year] <= MAX ( 'Year Table'[YearSelection] )
            && (
                Workforce[Termination Date] = BLANK ()
                    || Workforce[Termination Date].[Year] > MAX ( 'Year Table'[YearSelection] )
            )
    )
)

Best regards,

Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @CJ_96601,

Suppose the data table is named as 'Workforce', also you have an extra table which lists unique year values named as 'Year Table'.

Please refer to below measure:

Count workforce =
CALCULATE (
    COUNT ( Workforce[Name] ),
    FILTER (
        Workforce,
        Workforce[Hire Date].[Year] <= MAX ( 'Year Table'[YearSelection] )
            && (
                Workforce[Termination Date] = BLANK ()
                    || Workforce[Termination Date].[Year] > MAX ( 'Year Table'[YearSelection] )
            )
    )
)

Best regards,

Yuliana Gu

[CJ_96601]

hi @v-yulgu-msft, 

Thank you for your response.

I appreciate if you could send your sample pbix file.

Furthermore, i dont have a table with unique list of year, i use calendar.

The workforce table is link to calendar.  Active relation is HIREDate, inactive is Termination Date.

Regards

[v-yulgu-msft · Accepted solution]

Hi @CJ_96601,

Using a calendar table also applies to this scenario. Please remove the relationship between these two tables. If you cann't remove the relationship, please create another unrelated date table like what I have did.

Regards,

Yuliana Gu

[CJ_96601]

Thanks @v-yulgu-msft,

You're saying there is no need to establish a relationship between calendar and my workforce file?

If i may ask what is the use of calendar in power bi.

My apology, i'm new in power bi

Regards, 

[CJ_96601]

Hi, 

Please provide sample on how to count those who were terminated based on year selection.

Is there a way, if  i need to filter based on month / year or quarter / year

Regards,