Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am still pretty new to Power BI and am having an issue with a date filter. I am creating a Payroll report that has 2 tables. One table is week to week Payroll Data (Overtime, Hours Worked, etc.). This is refreshed weekly and has a field on every row called Week Ending to show what week each row applies to. Then I have a "Hired Employees" table that lists all new hires and their starting date. This is updated intermittently and does not have a week ending date, just the employee's start date. I am creating a dashboard that shows how much overtime each department has vs how many new hires they had in the past 15 and 30 days and have a date slicer for week ending in XX.
For the 30 days column, I need to only see people hired between 16-30 days prior to the Week Ending filter. So if someone was hired 14 days ago, they would only show under "Hired Past 15 Days" and not "Hired Past 30 Days". If they were hired 16 days ago, they would show up in the "Hired Past 30 Days" column only.
I created a date table and was able to create a measure for "Hired in the Past 15 Days from Selected Week Ending" with the following measure:
Week Ending | Department | Overtime Hours | New Hire 15-days | New Hire 30-days |
11/7/2020 | LG | 1 | ||
11/7/2020 | IT | |||
11/7/2020 | HR | 11.25 | ||
11/7/2020 | FD | 46.25 | ||
11/7/2020 | CM | |||
11/7/2020 | CR | |||
11/7/2020 | CF | |||
11/7/2020 | BR | 46 | 2 | |
11/7/2020 | SALES | 1 | ||
11/7/2020 | ACCT | 47 | ||
11/14/2020 | LG | |||
11/14/2020 | IT | 2 | ||
11/14/2020 | HR | 1 | ||
11/14/2020 | FD | 48 | ||
11/14/2020 | CM | 25 | ||
11/14/2020 | CR | 2 | 1 | 1 |
11/14/2020 | CF | 1 | ||
11/14/2020 | BR | 0 | 1 | |
11/14/2020 | SALES | 3 | ||
11/14/2020 | ACCT | 0 |
Solved! Go to Solution.
Hello, @BrianaHop
It's a pleasure to answer for you.
According to your description, I think you can create a measure, then use it in the filter pane.
Like this:
measure =
IF (
CALCULATE (
COUNT ( 'HiredEmployees'[Hire_Date] ),
DATEADD ( 'Date'[Date], -15, DAY )
)
= BLANK (),
0,
1
)
If you do not solve your problem, please feel free to ask me.
Best regards
Janey Guo
If this post helps,then consider Accepting it as the solution to help other members find it faster.
Hello, @BrianaHop
It's a pleasure to answer for you.
According to your description, I think you can create a measure, then use it in the filter pane.
Like this:
measure =
IF (
CALCULATE (
COUNT ( 'HiredEmployees'[Hire_Date] ),
DATEADD ( 'Date'[Date], -15, DAY )
)
= BLANK (),
0,
1
)
If you do not solve your problem, please feel free to ask me.
Best regards
Janey Guo
If this post helps,then consider Accepting it as the solution to help other members find it faster.
User | Count |
---|---|
98 | |
91 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |