Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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:
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 |
Solved! Go to 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 , 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 >=
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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
67 | |
60 |