Get all employee name in selected Date period

[Jyaul1122]

Hello all,Greeting,

I have Employee table and Company with are related with Company column.

Employee table:

Company	Employee name	Date of Join
P1	Emp1	30-Mar-24
P1	Emp2	30-Apr-24
P1	Emp9	30-May-24
P1	Emp4	30-Jun-24
P1	Emp5	30-Jul-24
P2	Emp7	30-Nov-24
P2	Emp8	30-Sep-24
P2	Emp12	30-Dec-24
P3	Emp21	30-Dec-24
P3	Emp22	30-Jan-25
P3	Emp23	25-Feb-25
P3	Emp29	26-Mar-25
P3	Emp25	07-Apr-25

and Compnay table:

Company

P1

P2

and also Employee table is related with Calendar table via Date of Join. I have Month slicer(single select) from Calendar Table in my report. Month slicer:

Requirements: I want to get table with  Project name and the all Employee name who join between dates Date(2024,01,01) and selected date from Slicer. For example when I select Sep 24 from slicer, Result will be: 

Company	Employee_
P1	Emp1
P1	Emp2
P1	Emp9
P1	Emp4
P1	Emp5
P2	Emp8

I tried to write measure like : Employee_=Calculate(max(employee name),datesbetween(Calendar[date],Date(2024,01,01),max(calendar[Date])) but thats not work. How can I do using DAX.

Could you please help ?

[DataNinja777]

Hi @Jyaul1122 ,

Here is the measure for your required output.

Show Employees = 
VAR SelectedDate = MAX('Calendar'[Date])
RETURN
IF(
    'Employee'[Date of Join] >= DATE(2024, 1, 1) &&
    'Employee'[Date of Join] <= SelectedDate,
    1, 
    0
)

Add Company and Employee name to a table visual, then apply the Show Employees measure as a filter where the value equals 1. This will display only rows that satisfy the condition without aggregating the names.

Best regards,

[Jyaul1122]

Thanks for your reply, I can not apply filter because I have a lot off measures and fields in table visual. Filter will impact to another measures also.

[123abc]

Use the following DAX to define a new measure that checks for employees whose joining date falls in the required range:

Employee_Filtered = VAR SelectedDate = MAX('Calendar'[Date]) RETURN CALCULATETABLE( VALUES('Employee'[Employee name]), 'Employee'[Date of Join] >= DATE(2024, 1, 1) && 'Employee'[Date of Join] <= SelectedDate )

---

Step 2: Create a Table Visual

1. Add a Table Visual to your report.
2. Drag the Company column from the Employee table.
3. Add the Employee_Filtered measure (this will dynamically return employee names).

---

Step 3: Adjust the Table to Show Filtered Data

If you want to ensure the table filters correctly when you select a month in the slicer, Power BI's relationship between the Calendar table and the Employee table will take care of the filtering.

---

Troubleshooting Tips:

1. Ensure the Employee[Date of Join] column is properly related to the Calendar[Date] column.
2. Make sure your Month Slicer comes from the Calendar table.
3. If the measure doesn't show the desired result in a table visual, you can use this as a calculated column instead.

---

Final Output:

When you select September 2024 in the slicer, the table will display:

Company Employee name

P1	Emp1
P1	Emp2
P1	Emp9
P1	Emp4
P1	Emp5
P2	Emp8

Let me know if you need further clarification or adjustments!

[Jyaul1122]

@123abc 

I tried with your idea but I got single value on Sep 24, P2 Emp 8.

I thinks , its filter rows based on Month selection. I also tried with remove relation between calendar table but got error: A table of multiple values was supplied where a single value was expected.

If you have pbix please share.

[123abc]

Thank you for the clarification. The issue lies with the filtering behavior. Since the slicer is single-select and filters the data to a single date (or month), we need to modify the approach slightly. Here is a correct DAX solution to achieve your goal without breaking relationships.

---

Key Considerations:

1. You need a list of employees who joined between January 1, 2024, and the end of the selected month.
2. A calculated column or measure needs to respect the selected slicer value but avoid filtering down to just one month.

---

Solution 1: Use a Calculated Table (Static Table)

This creates a calculated table in your model.

Filtered_Employees = VAR SelectedDate = MAX('Calendar'[Date]) RETURN SUMMARIZE( FILTER( 'Employee', 'Employee'[Date of Join] >= DATE(2024, 01, 01) && 'Employee'[Date of Join] <= SelectedDate ), 'Employee'[Company], 'Employee'[Employee name] )

 

Steps to Add This Table:

1. Go to the Modeling tab in Power BI.
2. Select "New Table" and paste the above DAX.
3. Use this new table (Filtered_Employees) in your visuals.

---

Solution 2: Create a Dynamic Measure

If you want to display this dynamically in an existing table visual:

Filtered Employee Name = VAR SelectedDate = MAX('Calendar'[Date]) -- Slicer selected date RETURN IF ( MAX('Employee'[Date of Join]) >= DATE(2024, 01, 01) && MAX('Employee'[Date of Join]) <= SelectedDate, MAX('Employee'[Employee name]), BLANK() )

Steps to Apply:

1. In your table visual:
   • Add Company from the Employee table.
   • Add the Filtered Employee Name measure.
2. This will dynamically filter and show results.

---

Solution 3: Avoiding Relationships Filtering the Calendar Table

If your slicer filters the Employee table directly due to relationships, try removing the slicer impact on the Employee table:

Updated Measure Using REMOVEFILTERS:

Filtered Employees = VAR SelectedDate = MAX('Calendar'[Date]) RETURN CALCULATETABLE( VALUES('Employee'[Employee name]), REMOVEFILTERS('Calendar'), -- Remove slicer impact FILTER( 'Employee', 'Employee'[Date of Join] >= DATE(2024, 01, 01) && 'Employee'[Date of Join] <= SelectedDate ) )

This measure ignores slicer filters and manually applies conditions.

---

Testing and Results

Once you apply the solutions:

• For September 2024, you should see:Company Employee Name

  P1	Emp1
  P1	Emp2
  P1	Emp9
  P1	Emp4
  P1	Emp5
  P2	Emp8

Let me know if you'd like me to create and share a sample PBIX file! 🚀

[Jyaul1122]

Its not working , same result as previous. Could you please share pbix ?