Date Slicer that filters different date fields

[Daniel_01]

I have a Date table, using CALENDARAUTO().

I need to be able to have a date slicer (between dates type) using this table that will filter 3 things:

1 - Absent days (this is a table that states every absent day the employee had, like this (example):

Employee_ID	Absence_Type	Date
345	Sick Leave	2023.12.01
345	Sick Leave	2023.12.02

2 - Employment start date, since I can't filter by current active employees, because I may want to see absenteeism in the past. So the max date I have put in the slicer should indicate the max Employment start date as well

3 -Employment date leave, for the same reason above. So the min date I have put in the slicer should indicate de min Employment date leave as well

(these last 2 columns are in a different table with Employee info, something like this:

Employee_ID	Emp_Dt_Start	Emp_Dt_Leave
345	2021.03.01	2023.09.30
646	2023.08.01	9999.12.31

(9999.12.31 = is still active)

So today I have 3 slicers like this:

But I would like to have only 1 slicer, because they are just repeating themselves. Is this possible? I though about using the measure Max and Min of the date table and connect it somehow to the start and leave dates, but I am not sure how to do it.

[Daniel_01 · Accepted solution]

Meanwhile, I found a solution:

I created this measure:

dax_was_active = IF(AND(SUMX(EMP_TABLE, INT(EMP_TABLE[EMPLOYMENT_DATE_START]) - INT(MAXX(DAX_Dates, DAX_Dates[Date]))) <= 0, SUMX(EMP_TABLE, INT(EMP_TABLE[EMPLOYMENT_DATE_LEAVING]) - INT(MIN(DAX_Dates[Date]))) >= 0), "Yes", "No")

Then I put this measure on the filter for the table visual, so I can filter by the "Yes". This way my table will only show the active employees during that period, and with the relation between the dax_dates table and the absences table it shows the correct number of absences per employee.

This was quite simple after all, sorry for bothering you.

[Ritaf1983]

Hi @Daniel_01 
Please refer to the linked video tutorials:

https://www.youtube.com/watch?v=eiDWMqgKlZI

https://www.youtube.com/watch?v=fOcgIuaJHEs&t=1s

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Daniel_01]

Hi @Ritaf1983 !

Thank you for replying. These videos don't answer my solution, however. The goal of the videos is to find people who have entered or left in those dates.

My goal, however, is to filter who was active during that period and know their absent days.

For example: I filter the dates between 2023.01.01 - 2023.06.31 (1st semester) and the visual table should show me all employees who were active during that period, even if just for a day (so their start date should be before 2023.06.31, and their leave date should be after 2023.01.01) and should tell me the number of days people were absent during that same period.

Sorry if I wasn't clear! I hope this clears things up.