Changing a calculated field based off date filter

[kneri3]

Hello,

I am working with employee data, more specifically, looking at Active vs. Terminated employees in a certain time period. Everything is dependent on the date filter. I have a hire date and termination date and I created a calculated measure (see below) to decide whether that person was active or terminated in the time frame.

This correctly gives me what I am looking for. The issue is that I cannot create any visuals/filters off this measure. I can only put it in a table. My whole analysis is centered around the number of actives/terminated in a certain group/over time/etc. So I have to be able to do counts of active and terminated, see it over time, broken out by group, etc. 

I found an article that had me create a column and give it the value of the measure:

After creating the column, it had me create a table with those values:

then create another measure:

This allows me to now be able to see active vs. terminated within visuals. 

However, this does not update when I change the date criteria. It is static because of the GT formula from above assigns a static value for each row.

This brings me back to my original issue of then not being able to dynamically see who was active/terminated during a certain time frame.

Can anyone give me any idea what to do in this situation? I copied dummy data that has the same fields as my dataset as well as an example of the filter. The table shows the measure 'conditional date_2' which dynamically changes as the 'original hire date' filter is applied. Again, I can't create any other visuals off of the measure.

Thank you in advance!

Name	Management_Level	Hire Date	Termination Date	Conditional Date_2	Count of Full_Name
John Smith	12 Individual Contributor	04/18/2022 0:00	05/24/2022 0:00	Terminated	1
Judy Chapman	8 Director	06/27/2007 0:00	03/20/2009 0:00	Terminated	1
Kelsey Patel	12 Individual Contributor	03/09/2020 0:00		Active	1
Mads Lightner	12 Individual Contributor	07/06/2021 0:00		Active	1
Ryan Cox	12 Individual Contributor	10/07/2019 0:00	11/30/2021 0:00	Terminated	1
Ashish Goodman	12 Individual Contributor	06/06/2022 0:00	08/26/2022 0:00	Terminated	1
Joseph Jones	12 Individual Contributor	05/13/2008 0:00	07/31/2011 0:00	Terminated	1
JJ White	12 Individual Contributor	06/07/2021 0:00		Active	1
Jennifer Lewis	12 Individual Contributor	04/29/2019 0:00	12/03/2021 0:00	Terminated	1

[Greg_Deckler]

@kneri3 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[kneri3]

Thank you for providing these. I leveraged your formula. See below:

However, I am getting an error and I think it has something to do with the fact that I am doing an employee count and not summing an amount. I added a column, 'Employee Count' and gave it a value '1' and I thought then it would sum, but I keep receiving this error: 

[kneri3]

@Greg_Deckler I was able to figure out the issue. There were blank dates in termination that caused it to error out. I was wondering if you have any idea why the status count total is saying 56 (bottom right table), instead of 11, which is what it is showing?