Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
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))

 

IDInOutCost
16/30/20209/30/2020 $      4,000
26/30/202010/2/2020 $         800
37/4/20201/1/2021 $      1,000
47/8/20209/30/2020 $      2,000
58/8/20209/30/2020 $      1,000
68/8/20209/9/2020 $      1,300
78/20/20201/2/2021 $         700
89/20/202010/2/2020 $         800
99/20/202012/1/2021 $      8,000
109/20/202011/1/2021 $      1,000
1110/1/202012/1/2021 $      2,000
1210/10/202011/1/2021 $      1,230
1310/10/20201/2/2021 $      1,450
1410/20/20202/1/2021 $      7,000
1511/4/2022012/1/2021 $      2,300
1611/4/2022012/3/2021 $         700
1711/4/202012/3/2021 $      2,400
1811/4/202012/3/2021 $      3,102
1911/4/20201/2/2021 $      4,000
2012/1/20201/2/2021 $      2,540
2112/1/2020  $      4,500
221/20/2021  $      3,000
231/20/20212/2/2021 $      1,200
241/20/20212/20/2021 $      3,400
251/20/2021  $      4,500
261/20/2021  $      8,000
271/20/2021  $      2,310
281/20/2021  $      1,200
292/2/2021  $         900
302/2/2021  $      3,000
1 ACCEPTED SOLUTION
amitchandak
Super User
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 >=

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
amitchandak
Super User
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 >=

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
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?

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.