cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Trend Filter Help

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
1 ACCEPTED SOLUTION
Super User

@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 >=

3 REPLIES 3
Super User

@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
Not applicable

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

Anonymous
Not applicable

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors