Forum Discussion

kneri3's avatar
kneri3
Frequent Visitor
3 years ago

Changing a calculated field based off date filter

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!

 

 

NameManagement_LevelHire DateTermination DateConditional Date_2Count of Full_Name
John Smith12 Individual Contributor04/18/2022 0:0005/24/2022 0:00Terminated1
Judy Chapman8 Director06/27/2007 0:0003/20/2009 0:00Terminated1
Kelsey Patel12 Individual Contributor03/09/2020 0:00 Active1
Mads Lightner12 Individual Contributor07/06/2021 0:00 Active1
Ryan Cox12 Individual Contributor10/07/2019 0:0011/30/2021 0:00Terminated1
Ashish Goodman12 Individual Contributor06/06/2022 0:0008/26/2022 0:00Terminated1
Joseph Jones12 Individual Contributor05/13/2008 0:0007/31/2011 0:00Terminated1
JJ White12 Individual Contributor06/07/2021 0:00 Active1
Jennifer Lewis12 Individual Contributor04/29/2019 0:0012/03/2021 0:00Terminated1

 

3 Replies