Trend Filter Help

[Anonymous]

Hi All!

I am struggling to create a visual that helps calculate how many ID's and the cost I had over the last year, considering the open and closed date. Through the HR Analytics - Active Employee, Hire and Termination trend  page, I can find the total over all time, but I'm struggling on filtering to only include the last year. So I'd like all ID's and the cost with:

• In>=February 2020 OR
• Out >=February 2020 OR
• Out is blank

The cost should only be included between the two dates. And if the Out date >=February 2020 but the In date is before February 2020, I'd like it to just show that's an ongoing cost from the start in February 2020 until the Out date. Hope that makes better sense! If not, please let me know. Thanks again for all the help!

Adapted this formula, but it's not working my my needs exactly:

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

ID	In	Out	Cost
1	6/30/2020	9/30/2020	$      4,000
2	6/30/2020	10/2/2020	$         800
3	7/4/2020	1/1/2021	$      1,000
4	7/8/2020	9/30/2020	$      2,000
5	8/8/2020	9/30/2020	$      1,000
6	8/8/2020	9/9/2020	$      1,300
7	8/20/2020	1/2/2021	$         700
8	9/20/2020	10/2/2020	$         800
9	9/20/2020	12/1/2021	$      8,000
10	9/20/2020	11/1/2021	$      1,000
11	10/1/2020	12/1/2021	$      2,000
12	10/10/2020	11/1/2021	$      1,230
13	10/10/2020	1/2/2021	$      1,450
14	10/20/2020	2/1/2021	$      7,000
15	11/4/20220	12/1/2021	$      2,300
16	11/4/20220	12/3/2021	$         700
17	11/4/2020	12/3/2021	$      2,400
18	11/4/2020	12/3/2021	$      3,102
19	11/4/2020	1/2/2021	$      4,000
20	12/1/2020	1/2/2021	$      2,540
21	12/1/2020		$      4,500
22	1/20/2021		$      3,000
23	1/20/2021	2/2/2021	$      1,200
24	1/20/2021	2/20/2021	$      3,400
25	1/20/2021		$      4,500
26	1/20/2021		$      8,000
27	1/20/2021		$      2,310
28	1/20/2021		$      1,200
29	2/2/2021		$         900
30	2/2/2021		$      3,000

[amitchandak · Accepted solution]

@Anonymous , Try with Min on start date

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=Min('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

if need do same for max. Also check for <= or >=

[Anonymous]

Exactly what I was looking for! Thanks so much, @amitchandak !

[Anonymous]

Hi @amitchandak,

Thanks again for all your help! When applying this to real life, for some reason the visual is inaccurate, not considering tickets created this month (or in this case, employees onboarded this month). Can you help me see where it went wrong?