Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 Employee
Microsoft Employee

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 Employee
Microsoft Employee

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.