Forum Discussion
Filter Calculated Column Dynamically - Show Results for Former Employees based on Date Range
Hi,
You must have a Calendar Table with a relationship from the Date column of your data Tables to the date column of the Calendar Table. In the Calendar Table, you must have a Week number column (use the WEEKNUM()) function. In the slicer drag the Weeknum column from the Calendar Table. Now with this formula, you will get the first date of the selected week
=MIN(Calendar[Date])
Hope this helps.
Hi Ashish_Mathur,
I'm afraid this isn't what I'm looking for. I'll add some images to help.
Here are my data tables and relationships. I have a Calendar Table, User Table (unique list of employees) and a TimeEntries table, which shows the hours everyone has worked, where and when. As you can see both the Calendar and User tables link into the TimeEntries table.
This is a view of the User table. We don't have a final date of employment in our system, so I've worked around this by creating a column that says if they're not an active user, the give the last updated date value - which would have been when they were marked as not active. Otherwise, give today's date.
I'll have a slicer on the report that filters by Week Commencing date, from either the Calendar or TimeEntries table.
How can I get the list of Users to filter depending on the Week Commencing date so show only the employees with an Active Date greater than or equal to the slicer value? There will be instances where the slicer has multiple values so I would need to show those employees with Active Dates greater or equal to the MIN value in the slicer.
Does that help?
Hi,
Share the link from where i can download your file. Also, it will be great if you can show the expected result in a simple Table for a few items chosen in the slicer.
Hi Ashish_Mathur,
I can't attach a link to the file I'm afraid, there's sensetive information on there. So I've mocked up a really simple example of what I'm trying to achieve. Click here to access it.
Imagine in Week 1 I have 8 active employees. In that week only 7 of them record hours worked. I would want my chart to show all 8 employees even with the employee who didn't record time. In Week 2, 2 of the employees are now longer active, so when this week is selected on the report they shouldn't appear at all.
I want a date slicer to allow me to swtich between these.
Does that help?
Hi,
That link is not valid.