Date Range from the Date Picker to be compared & filtered with 2 dates ( Start & End ) within a Report

[sourchandra]

I am facing this following issue & would require suggestion for the same :

As you can see in the following report we have 2 dates "Effect..Period Start date" & "Effect..Period end date".

We have a date picker which gives you 2 dates ( start & end ) when the range is  selected .

We need to filter the "Effect..Period Start date" & "Effect..period end date" of the report in between the 2 selected range dates

For eg we have selected 15/03/21-31/03/21 in the date picker then "Effect..Period Start date" & "Effect..Period end date" should have dates between 15/03/21-31/03/21.

I used the following script in the "Effect..Period Start date".[TheDate-Date(Floor(Availability_Date)).autoCalendar.Date] is actually only the Date field.

=If (([TheDate-Date(Floor(Availability_Date)).autoCalendar.Date] <= Date(Floor([ProfileAttribute.EFFECTIVEPERIODSTARTDT]))

and [TheDate-Date(Floor(Availability_Date)).autoCalendar.Date] >= Date(Floor([ProfileAttribute.EFFECTIVEPERIODENDTDT])))

,(Date(Floor([ProfileAttribute.EFFECTIVEPERIODSTARTDT]))))

& the following for the “Effect..Period End Date “

=If (([TheDate-Date(Floor(Availability_Date)).autoCalendar.Date] <= Date(Floor([ProfileAttribute.EFFECTIVEPERIODSTARTDT]))

and [TheDate-Date(Floor(Availability_Date)).autoCalendar.Date] >= Date(Floor([ProfileAttribute.EFFECTIVEPERIODENDTDT])))

,(Date(Floor([ProfileAttribute.EFFECTIVEPERIODENDTDT]))))

but the Start & the End Dates only matching are coming up in the report like for eg.

I have checked between the selected Date Picker Range between 15/03 & 31/03 there are many start dates with end dates where end dates >start dates but these are not appearing like start date as 16/03 & end date as 19/03 , only those cases where both the start & the end dates are equal are coming up.