cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CJ_96601
Helper V
Helper V

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

 

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

 

  

 

 

2 ACCEPTED SOLUTIONS
v-yulgu-msft
Microsoft
Microsoft

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

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yulgu-msft
Microsoft
Microsoft

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

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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, 

 

 

 

 

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, 

 

 

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.