Answered

Filter gallery with date

(0) Share

Report

Posted on by michalborek

Hello,

I have a sharepoint list with events. Gallery is showing that event month by month. My code in item property of gallery is:

With(
 {
 _selectedDate: DateValue(
 "01-" & Text(
 ddMonthFilter.Selected.MonthNumber;
 "00"
 ) & "-" & ddYearFilter.Selected.Value
 )
 };
 SortByColumns(Filter(
 ft_postoje_zakladow_test;
 DateValue(ThisRecord.od_kiedy) >= _firstDayOfMonth;
 DateValue(ThisRecord.do_kiedy) <= _lastDayOfMonth;
 cbxActivityFilter.Selected.Atelier in ThisRecord.Title
 ); "field_1")
)
//.od_kiedy - since when .do_kiedy - until when

I would like to also include events which are in between two months so for example i have three events:

ThisRecord.od_kiedy-ThisRecord.do_kiedy

15.05.2023-3.06.2023

8.06.2023-12.06.2023

15.06.2023-3.07.2023

Only middle show in June gallery.

I tried with expanding condition for last/next month serach, but result wasn't sastysifing.

Filter(
 ft_postoje_zakladow_test;
 DateValue(ThisRecord.od_kiedy) >= _firstDayOfMonth&&DateValue(ThisRecord.do_kiedy) <= DateAdd(_lastDayOfMonth;1;TimeUnit.Months)&&cbxActivityFilter.Selected.Atelier in ThisRecord.Title||DateValue(ThisRecord.od_kiedy) >= DateAdd(_firstDayOfMonth;-1;TimeUnit.Months)&&cbxActivityFilter.Selected.Atelier in ThisRecord.Title
 );
 "field_1")
))

Best Regards

Michał Borek

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Sundeep_Malik 6,501 on at

Like
a
(0)

Report
Copy link

Link copied!
Hey @michalborek

Try this:

This should give you what you are looking for:

With( { _selectedDate: DateValue( "01-" & Text( ddMonthFilter.Selected.MonthNumber; "00" ) & "-" & ddYearFilter.Selected.Value ), _nextMonthDate: DateAdd( DateValue( "01-" & Text( ddMonthFilter.Selected.MonthNumber; "00" ) & "-" & ddYearFilter.Selected.Value ); 1; Months ), _prevMonthDate: DateAdd( DateValue( "01-" & Text( ddMonthFilter.Selected.MonthNumber; "00" ) & "-" & ddYearFilter.Selected.Value ); -1; Months ) }; SortByColumns( Filter( ft_postoje_zakladow_test; (DateValue(ThisRecord.od_kiedy) >= _firstDayOfMonth && DateValue(ThisRecord.do_kiedy) <= _nextMonthDate) || (DateValue(ThisRecord.od_kiedy) >= _prevMonthDate && DateValue(ThisRecord.do_kiedy) <= _lastDayOfMonth) || (DateValue(ThisRecord.od_kiedy) < _firstDayOfMonth && DateValue(ThisRecord.do_kiedy) > _lastDayOfMonth) && cbxActivityFilter.Selected.Atelier in ThisRecord.Title ); "field_1" ) )

I hope this helps 🙂

Was this reply helpful? Yes No
michalborek 44 on at

Like
a
(0)

Report
Copy link

Link copied!
Dear @Sundeep_Malik ,

Thank you for your intrest. I tried your code. Just made small adjustment. (Time unit and filter atelier as one for every condtion.
With( { _selectedDate: DateValue( "01-" & Text( ddMonthFilter.Selected.MonthNumber; "00" ) & "-" & ddYearFilter.Selected.Value ); _nextMonthDate: DateAdd( DateValue( "01-" & Text( ddMonthFilter.Selected.MonthNumber; "00" ) & "-" & ddYearFilter.Selected.Value ); 1; TimeUnit.Months ); _prevMonthDate: DateAdd( DateValue( "01-" & Text( ddMonthFilter.Selected.MonthNumber; "00" ) & "-" & ddYearFilter.Selected.Value ); -1; TimeUnit.Months ) }; SortByColumns( Filter( ft_postoje_zakladow_test; (DateValue(ThisRecord.od_kiedy) >= _firstDayOfMonth && DateValue(ThisRecord.do_kiedy) <= _nextMonthDate) || (DateValue(ThisRecord.od_kiedy) >= _prevMonthDate && DateValue(ThisRecord.do_kiedy) <= _lastDayOfMonth) || (DateValue(ThisRecord.od_kiedy) < _firstDayOfMonth && DateValue(ThisRecord.do_kiedy) > _lastDayOfMonth); cbxActivityFilter.Selected.Atelier in ThisRecord.Title ); "field_1" ) )
but not every result is good.
1. 1.04.2023-1.04.2023 shouldn't display in May, same case in June 18.05.2023-19.05.2023.
2. In May, one event from 20.05.2023 to 16.06.2023 should be also visable in May. (I'm not sure why it's happening)
Do you know meybe how to improve code?

Thank you in advance

Was this reply helpful? Yes No
Verified answer

michalborek 44 on at

Like
a
(0)

Report
Copy link

Link copied!
I come by giving extended range and picking the start day or end day in a given month gallery.
Filter( ft_postoje_zakladow_test; (DateValue(ThisRecord.od_kiedy) >= _prevMonthDate && DateValue(ThisRecord.do_kiedy) <= _nextMonthDate)&&(Month(DateValue(ThisRecord.od_kiedy))=Month(_firstDayOfMonth)||Month(DateValue(ThisRecord.do_kiedy))=Month(_firstDayOfMonth)); cbxActivityFilter.Selected.Atelier in ThisRecord.Title

Was this reply helpful? Yes No