Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
@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
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:
@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?
User | Count |
---|---|
98 | |
91 | |
84 | |
72 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |