Paginated Reports (report builder) - select only records between dates

[ducky]

Hi all,

New on paginated reports. 

heres my situation - got a model published on services. I connect to that dataset with PBI Report builder. 

I have a employee table looking like this: 

Employee Id	start date	end date	company id
A123	1/1/2021	1/31/2021	bla
A123	2/1/2021	5/1/2021	bla
B111	5/2/2021	1/31/2022	bla
B111	2/1/2021	5/1/2021	bla

my table contains the historised employee data where periods are not overlapping. 

I want to have a parameter that user can select a date and I pass this parameter in my query and select only the record of each employee that is in that period (eg: date selector: 2/15/2021 would give 2nd record of each employee)

I've made this query but for some reason it doesnt work. 

EVALUATE
SUMMARIZECOLUMNS(
'employee'[company_id],
'employee'[department],
'employee'[employee_name],
'employee'[employee_id],
'employee'[start_dt],
'employee'[end_dt],
FILTER( 'employee',
@date_selector >= Format('employee'[start_dt],"yyyy-mm-dd") && @date_selector <= FORMAT('employee'[END_DT],"yyyy-mm-dd") ))
ORDER BY
'employee'[company_id] ASC

Any ideas ? or maybe some direction where I could find an answer? 

PS: I've been trying other methods and searching online but no satisfactory results( some due performance) 

[ducky · Accepted solution]

@Sahir_Maharaj  - Thanks for your suggestions. However I was more looking for a practical approach to solving my issue. 

Here's what I finally made out to work using DAx studio - only pasting the filtering solution. 

     FILTER(
     VALUES('employee'[END_DT]), 
      @date_selector <= 'employee'[END_DT]
    ),
    FILTER(
     VALUES('employee'[START_DT]), 
      @date_selector >= 'employee'[START_DT]
    )
    )

[Sahir_Maharaj]

Hello @ducky,

Here are some suggestions:

1. Ensure that the format of the date parameter @date_selector matches the format of the start_dt and end_dt columns in the 'employee' table.

2. Verify that the date comparison in the FILTER function is evaluating correctly. Instead of using the FORMAT function within the FILTER, you can directly compare the dates. Here's the adjusted query:

EVALUATE
SUMMARIZECOLUMNS(
    'employee'[company_id],
    'employee'[department],
    'employee'[employee_name],
    'employee'[employee_id],
    'employee'[start_dt],
    'employee'[end_dt],
    FILTER(
        'employee',
        @date_selector >= 'employee'[start_dt] && @date_selector <= 'employee'[end_dt]
    )
)
ORDER BY 'employee'[company_id] ASC

3. Ensure that there is a proper relationship established between the 'employee' table and any other relevant tables.

Should you require further details or assistance please do not hesitate to reach out to me.