I'm trying to add a filter to a gallery so that only items within the current fiscal year appear. There is a dropdown with year values on the gallery screen so that as the years pass, they can change the filter to go back and look at previous years' records.
I based my solution off of this article from Matthew Devaney: Power Apps Filter Gallery By Dates In Current Year - Matthew Devaney.
I just modified the dates in the Start and End variables to be the start and end of the fiscal year and added a dropdown with a selection of 10 years to indicate the current (if unchanged) or desired year. The dropdown also has a formula that assigns the current fiscal year value as the default.
However, I cannot get the filter to work. I've tried it exactly as shown (with the exception of my Fiscal Year selection control providing the year value and changes to the Start and End date variable values). I also tried setting it up using global variables which are set when the gallery screen becomes visible or when the Fiscal Year selection is made. I have multiple galleries that will eventually use this control, so global variables will probably be used.
I also have filters on the gallery to provide only items that the current user has created. This worked fine until I added the date filter. I removed all the filters and applied only the date filters (as mentioned above) and they returned zero records. Removed the date filters and all the appropriate records displayed. The field I am filtering on is from a SharePoint list and is a SharePoint date field set to date only (no time).
This is the code used for each attempt:
Original working filter
Sort(
Filter(
'LMS Request - Learning Plus',
/* Users can search for a specific Request ID using the search box */
StartsWith(
'Request ID',
TextSearchBox1.Text
),
/* Only items created by the user or where the user has been assigned as the Request Owner are visible */
Or(
'Request Owner'.Email = varCurrentUserEmail,
'Created By'.Email = varCurrentUserEmail
)
),
Modified,
SortOrder.Descending
)
Filter with added Start and End Date variables
Sort(
Filter(
'LMS Request - Learning Plus',
/* Only dates falling between Start and End dates should be included in the gallery */
'Request Date' >= varStartDate,
'Request Date' <= varEndDate,
/* Users can search for a specific Request ID using the search box */
StartsWith(
'Request ID',
TextSearchBox1.Text
),
/* Only items created by the user or where the user has been assigned as the Request Owner are included in the gallery */
Or(
'Request Owner'.Email = varCurrentUserEmail,
'Created By'.Email = varCurrentUserEmail
)
),
Modified,
SortOrder.Descending
)
Variable Population in Screen OnVisible
Set(
varStartDate,
Date(
FYValue.Selected.Title,
4,
1
)
);
Set(
varEndDate,
Date(
FYValue.Selected.Title,
3,
31
)
);
With the original filters and the date filters included, records are returned as if the date filters don't exist. With only the date filters in place, no records are returned.
Any help in understanding what I'm doing wrong is greatly appreciated!
