Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
I want to see which employees were working in my company during a filtered time period.
I tried using these measures:
SelectedDate = 'Date'[Date]
Active = IF(SelectedDate > MIN('Employee'[StartDate] && SelectedDate < 'Employee'[EndDate]; "y"; "n")
But this will always give "y" as a response
Let's say we'd look at 01-02-2020 then I'd want the Active- column to display the following:
Employee | StartDate | EndDate | Active? |
employee a | 1-1-2020 | y | |
employee b | 1-1-2016 | 31-06-2018 | n |
employee c | 1-4-2018 | y | |
employee d | 1-12-2015 | y | |
employee e | 1-1-2018 | 31-12-2018 | n |
employee f | 1-7-2017 | 31-12-2019 | n |
employee g | 1-1-2019 | y |
Solved! Go to Solution.
Hi @Anonymous,
you can create a measure, replace Dates[Date] with your slicer Dates Table.
Active =
SWITCH(
TRUE();
Max('Table'[EndDate]) = BLANK(); "Y";
MAX(Dates[Date]) < MAX( 'Table'[EndDate]); "Y"; "N"
)
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!
Hi @Anonymous ,
First you need to create a CALENDAR table and use date field as silcer.
CALENDAR = CALENDARAUTO()
Please refer to the measure below.
active =
var startdate = SELECTEDVALUE('Table'[StartDate])
var enddate = SELECTEDVALUE('Table'[EndDate])
var mindate = MIN('CALENDAR'[Date])
var maxdate = MAX('CALENDAR'[Date])
return
IF((enddate<>BLANK()&&enddate<mindate)||startdate>maxdate,"n","y")
Result would be shown as below.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous,
you can try these Dax Code in a new calculated Column. This should work for you.
Active =
var selectedDate = DATE(2020;02;01)
return
IF('Table'[EndDate] = BLANK() || 'Table'[EndDate] > selectedDate; "Y"; "N")
The output look like this.
Regards
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!
Hi @rainer1 ,
This works perfectly if I have one specific date, but I would like to be able to see this information for dates a user can pick.
Do you know how to solve this issue?
Kind regards
Hi @Anonymous,
you can create a measure, replace Dates[Date] with your slicer Dates Table.
Active =
SWITCH(
TRUE();
Max('Table'[EndDate]) = BLANK(); "Y";
MAX(Dates[Date]) < MAX( 'Table'[EndDate]); "Y"; "N"
)
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!
It was useful? Press Thumbs Up!
Refer My HR Blog: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
You can have an employee of every month or day
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |