Filter gallery to show overdue and current month items

[JuliosJamby]

Hi, I would like to filter my gallery to show overdue items as well as current month item. I have a month column to show the month for the dates, as well as an overdue column to show how many days are the item overdued. (overdue items will have a -ve value, which means its less than 0). 

I tried using - Filter(Sample, Int(Overdue)<0 && field_7(Month column) = Text(Today(), "mm"))

However, the above method don't show in the gallery.  How do i go about solving this issue?  

The overdue item can go anywhere from -1 to -365 and above

[WarrenBelz]

Hi @JuliosJamby ,

Your code

Filter(
   Sample, 
   Int(Overdue) < 0 && 
   field_7(Month column) = Text(Today(), "mm")
)

assumes that your Overdue column is a Number field and can have negative values and that the field field_7(Month column) - if that is the actual name, you need single quotes around it 'field_7(Month column)' is a Single Line of Text containing the month as 01, 02, 03 etc. Is all of that true?

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[JuliosJamby]

Yes correct. 

My overdue column has a number field while my month is generated using the text formula in excel 

[WarrenBelz]

@JuliosJamby ,
I suspected a calculated column - these do not "play well" with Power Apps at the best of times - the idea of a Data Source is just that - to store data. Put the value in a Text field and all will be well - just have a Text control with the Default of 

Text(YourDateFieldName,"mm")

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[JuliosJamby]

I use this to generate my month column. 

Text(YourDateFieldName,"mm")

As for my overdue column, its a calculated field in sharepoint. So how do I go about solving this. Sorry my datasource I'm using sharepoint. 

Does this means that my comparison should only be done with columns that has the same type? 

[WarrenBelz]

@JuliosJamby ,

Firstly I assume you replaced YourDateFieldName with the actual field name, also that your Overdue is calculated on a Date field in the list with the difference between Today and the date?

Dates are only partly Delegable in SharePoint, however if the number of total entries for the month are less than your Delegation Limit (maximum 2,000), you can do this

With(
   {
      wList:
      With(
         {wNow:Text(Today(), "mm")},
         Filter(
            Sample, 
            YourNewMonthColumn = wNow
         )
      )
   },
   Filter(
      wList,
      DateDiff(
         YourDueDateField,
         Today(),
         Days
      ) > 0   
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[JuliosJamby]

If my total entries for the month is more than 2000, can I still use this method? 

[WarrenBelz]

@JuliosJamby ,

No, the top filter will only collect 2,000 records maximum. On looking at this again (refocus thought process), you could however do this if you simply want all the items for this month that are before today and this would be be fully Delegable.

With(
   {wNow:Text(Today(), "mm")},
   Filter(
      Sample, 
      YourNewMonthColumn = wNow &&
      YourDueDateField < Today()
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[JuliosJamby]

Hi @WarrenBelz,

I did the following formula to find my overdue and current items based on the overdue column. However, nothing appears in my gallery if i use this formula.

Filter(colSample,Int(Overdues)>=31 && Int(Overdues)<0))

But if i use the formula below, items will show in the gallery. But it only shows either current or overdue items, but i want both 

Filter(colSample,Int(Overdues)>=31 || Int(Overdues)<0))

How do i go about solving this? 

[WarrenBelz]

@JuliosJamby ,

As I said earlier, throw away your SharePoint calculated columns for any use in Power Apps - as well as not being Delegable, I have never found them (I used them early in my journey) to behave as expected. Power Apps is perfectly capable of doing exactly the same calculation on the same fields. What formula is in your Calculated Column?