Calculate Total Resign/Active Employee per quarter

[laurent_rio]

Hi,

I get the employee list report by end of quarter, for example Q4 2020 = date of report 31/12/2020. This is my list 

How to Calculate The Total of resign people in Q4 2020/Active People in Q2020 

The answer should be = 3/10

My idea is try to make condition DAX to count 

Calculate (Sum(Total resign), Filter(Termination date.(quarter) = Report date.(quarter) & termination date.(year) = Report date year)))

But again i cant figure out to get the correct DAX 

Can anyone help ?

Thanks

[Jihwan_Kim · Accepted solution]

Hi, @laurent_rio 

I am not sure about how your data model looks like.

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

I created it without DIM Calendar Table.

Resign People in Q4 =

CALCULATE (

COUNTROWS ( VALUES ( Employee[Employee] ) ),

FILTER (

Employee,

QUARTER ( Employee[Resign Date] ) = QUARTER ( Employee[Date] )

)

)

Active People in Q4 =

CALCULATE (

COUNTROWS ( VALUES ( Employee[Employee] ) ),

FILTER (

Employee,

Employee[Status] = "Active"

)

)

https://www.dropbox.com/s/k48qt4tloga90w0/laurentv2.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[amitchandak]

@laurent_rio ,Refer if my blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...