cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JesseV
Frequent Visitor

Filter rows based on dates between two dates

I have two fields "FurloughStart_date" , "FurloughEnd_date" with dates (denoting the start and end of a furlough request) 

 

I also have a Dates table with these columns :

 

#From (dd-mm)To (dd-mm)
101-0102-01
202-0103-01
303-0104-01
404-0105-01
505-0106-01

 

Based on the entered furlough dates  I want to show all the rows with dates:

starting from (From column) FurloughStart 

Ending with (To column) FurloughEnd

 

I.E. when "FurloughStart_date" = 02-01 and  "FurloughEnd_date" = 05-01 I want to return rows # 2,3 and 4

 

I'm thinking I'll have to generate all the dates between the two furlough dates but I'm unsure how to generate them (and how to store them) 

 

All help is greatly appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
RusselThomas
Microsoft
Microsoft

Hi @JesseV ,

I'm sure there might be a better way to approach it overall, but I'm not sure how you're applying that table in the context of your app, so I'll assume you need it this way and start by just trying to answer the exact question.

 

Assuming your dates table is called "myDatesTable" and you have a datepicker called startDate and a datepicker called endDate, you can try something like this - 

Add a button and set it's OnSelect: property to;

ClearCollect(collectFilteredDates, Filter(myDatesTable, From>=startDate.SelectedDate && To <=endDate.SelectedDate))

 

If you don't want or need a reference table, you can pretty much get the same result by building the filtered result on the fly by doing the following - add a button to your page and set its OnSelect: property to

ClearCollect(collectMyDateRange, 
    AddColumns(
    Sequence(DateDiff(startDate.SelectedDate, endDate.SelectedDate, Days), 0), //number of days between the dates
        "From", startDate.SelectedDate + Value, //From date plus current sequence number
        "To", startDate.SelectedDate + Value + 1 //To date plus current sequence number + 1
    )
)

 Hope this helps,

RT

View solution in original post

9 REPLIES 9
RusselThomas
Microsoft
Microsoft

Hi @JesseV ,

I'm sure there might be a better way to approach it overall, but I'm not sure how you're applying that table in the context of your app, so I'll assume you need it this way and start by just trying to answer the exact question.

 

Assuming your dates table is called "myDatesTable" and you have a datepicker called startDate and a datepicker called endDate, you can try something like this - 

Add a button and set it's OnSelect: property to;

ClearCollect(collectFilteredDates, Filter(myDatesTable, From>=startDate.SelectedDate && To <=endDate.SelectedDate))

 

If you don't want or need a reference table, you can pretty much get the same result by building the filtered result on the fly by doing the following - add a button to your page and set its OnSelect: property to

ClearCollect(collectMyDateRange, 
    AddColumns(
    Sequence(DateDiff(startDate.SelectedDate, endDate.SelectedDate, Days), 0), //number of days between the dates
        "From", startDate.SelectedDate + Value, //From date plus current sequence number
        "To", startDate.SelectedDate + Value + 1 //To date plus current sequence number + 1
    )
)

 Hope this helps,

RT

Devel
Frequent Visitor

Hi.

 

I've used a gallery to show the data from a Sharepoint list and used the Filter-function to show the items between two dates. Im guessing that the dates are stored in a sharepoint list? Im not sure if this is exactly what you are after but here is my example:

 

Skjermbilde.PNG

In this example the filter is based on the datepickers. The first datepicker is checking wether the first column/date is greater than the selected date and checks if the datepicker number 2 is greater than the second column. In the gallery the date in the top corner of an item is the second column dates. The date at the bottom of the item is from the first columns with dates. The code that is used is;

 

Filter(Sharepointsite/yourlistname; Columnfirstdate>= DatePicker1.SelectedDate && Columnseconddate <= DatePicker2.SelectedDate)

 

This way you can show items using datepickers and exclude those items that are not between these selected dates.

Excellent, and so Elegant aswell! 

 

I was thinking too complicated on the filter, using that I can achieve the rest of what I'm looking for (the actual example is an imported excel filter which I can just filter on) 

 

Thank you so much for your assistance! 

JesseV
Frequent Visitor

Thank you for sharing your solution! My app uses a datatable but the data part of the filter will work the same way as you described. 

Ah, trouble in paradise.

 

when I attempt to filter the imported excel I cant enter the table names into the filter when compared to the datepicker value

JesseV_0-1656510361552.png

 

Did I misunderstand the solution @RusselThomas ?

 

The error given is that it expects a 'Number', 'Date' , 'Datetime' or 'Time' value.. (wheareas I'm entering the table with all the 'from' or 'to'datevalues) 

 

Update:

 

The field I'm comparing with is reckognised as a string datatype (not a datetype) .. no matter what I input in the excel column, atleast a step closer to a solution! 

 

JesseV_0-1656513639186.png

 

RusselThomas
Microsoft
Microsoft

Hi @JesseV ,

You can convert the string to date format inline with DateValue() - so something like this;

Filter(RoosterWardenburg;
 DateValue(Van) >= datum_van.SelectedDate &&  DateValue(tot) <= datum_tot.SelectedDate
) 

Just doublecheck your dateformat conversion - if the format is dd-mm-yyyy and you're converting to mm-dd-yyyy (or vice versa) then you might end up with weird dates.  If this is the case you can manually strip the string into it's date components and convert it using Date(year, month, day).

Hope this helps,

RT

Uff @RusselThomas , brilliant!

 

I was using DateValue(RoosterWardenburg.Van)  instead of DateValue(Van) 

 

Which apperantly means it enters the entire collumn (as a table) instead of the values per row. It works now, an excellent day to end the workday (over here atleast!)

 

Thank you for your invaluable support; greetings from the Netherlands and have a great day! 

RusselThomas
Microsoft
Microsoft

Graag gedaan @JesseV  😊

haha ❤️

 

werkse vandaag!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (4,562)