start date and end date range selection to get count

[chintusasmal123]

 This is my data set , 
Where I have active relationship with end date and inactive with start date 
I'm filter users with slicer selected value for LineManager column and when friday column value is true

Friday = VAR FilteredData =

    CALCULATETABLE(

        FILTER(

      'RWDataset',

        (RWDataset[LMEmail] = SELECTEDVALUE(DupEmails[Value])

       // || RWDataset[UHEmail] = SELECTEDVALUE(DupEmails[Value]) || RWDataset[SHEmail] = SELECTEDVALUE(DupEmails[Value])

         ) &&

        'RWDataset'[RWFriday] = TRUE()

       && (RWDataset[start-date] <= [1 Selected Start Date] ||

          RWDataset[end-date] >= [2 Selected End Date]))

    ,USERELATIONSHIP('Date'[Date],RWDataset[start-date]))

VAR DistinctEmployees =

    DISTINCT(SELECTCOLUMNS(FilteredData, "EmployeeIDColumn", 'RWDataset'[RequestorEmail]))

RETURN COUNTROWS(DistinctEmployees)


Using this measure to find disctinct employee name 
When the date range slicer is from 01-03-2025 to 30-06-2025 value is coming correct i.e 7 
but when i select date range from 01-04-2025 to 30-04-2025 value coming is zero , but it should be 5 , either start date must be before the range's start date and less than range's end date and end date can be greater or equal to but greater than start date , I can't really getting confuse with the logic here.

[v-hashadapu]

Hi @chintusasmal123 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @chintusasmal123 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @chintusasmal123 , Thank you for reaching out to the Microsoft Community Forum.

Please let us know if your issue is solved. If it is, consider marking the answers that helped 'Accept as Solution', so others with similar queries can find them easily. If not, please share the details.
Thank you.

[lbendlin]

You need to use a disconnected calendar table for this.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[chintusasmal123]

ID	Requestor Name	LineManager	start-date	end-date	RWWithin24Hr	RWWorkigOption	RWMonday	RWTuesday	RWWednesday	RWThursday	RWFriday
6630	MD	CS	01/02/2025	12/03/2025	TRUE		FALSE	FALSE	TRUE	FALSE	TRUE
7022	NS	CS	27/02/2025	10/03/2025	TRUE		TRUE	TRUE	TRUE	TRUE	TRUE
7616	SR	CS	24/04/2025	09/05/2025	TRUE		TRUE	TRUE	TRUE	TRUE	TRUE
6412	PP	CS	01/01/2025	27/06/2025	TRUE		FALSE	FALSE	FALSE	TRUE	TRUE
6420	CD	CS	02/01/2025	27/06/2025	TRUE		FALSE	FALSE	FALSE	TRUE	TRUE
6424	SV	CS	02/01/2025	27/06/2025	TRUE		FALSE	FALSE	FALSE	TRUE	TRUE
7629	NM	CS	23/04/2025	30/06/2025	TRUE		TRUE	TRUE	TRUE	TRUE	TRUE

When a user select a date range from 01-03-2025 to 30-04-2025  then any employee who's start date falls inbetween the date range or earlier showed be shown and also who's end date falls inbetween or greater should be shown , for example here Chintan is doing work from home 02/01/2025 till 27/06/2025 that mean he falls between the date range of 01-03-2025 to 30-04-2025

6420

CD

CS

02/01/2025

27/06/2025

[lbendlin]

[chintusasmal123]

Now if I select date range from 01/04/2025 to 30/06/2025 , I'm getting wrong value rather I should have got 5 all record either start date or end date falls in that range 

[lbendlin · Accepted solution]

Instead of

CALENDAR([start-date],[end-date])

you can use a list

{[start-date],[end-date]} 

if you really want to check only for these two dates.