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.
