Answered

How can I filter a collection for records from the previous specific date?

(0) Share

Report

Posted on by mrwilliams

I have a collection with a field called 'submitted' which is a date and time field. I want to count the rows of records which have a submitted date after the 1st Septmber - but this has to be the most recent september that has past.

This is basically to count how many records have been submitted in the acedemic school year (which in the UK starts in September.)

I am using this code to filter the number of rows submitted for the current month.

CountRows(Filter(colWellbeing,Month(Submitted)=Month(Now())))

But I'm not sure how to adapt the date to get records since the previous 1st September.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

rzuber 552 Moderator on at

Like (1)

Report
Copy link

Link copied!
What about something like this?

With( {_start: Date(Year(Today()) - If(Month(Today()) < 9, 1), 9, 1)}, CountRows(Filter(colWellbeing, Submitted >= _start)) )

[EDIT] Sorry for moving the year to the end of the function parameters. My brain was malfunctioning yesterday. 😁

Was this reply helpful? Yes No
mrwilliams 50 on at

Like (0)

Report
Copy link

Link copied!
Thanks @rzuber . I will try it. I find a solution with the following method:

If( Or( Month(Now()) = 9, Month(Now()) = 10, Month(Now()) = 11, Month(Now()) = 12 ), CountRows(Filter(colWellbeing,Submitted>Date(Year(Now()),"09","01"))), CountRows(Filter(colWellbeing,Submitted>Date(Year(Now())-1,"09","01"))) )

Was this reply helpful? Yes No
WarrenBelz 154,399 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @mrwilliams ,

Posting a more condensed version of that - also @rzuber is correct.

CountRows( Filter( colWellbeing, Submitted > Date( Year(Now()) - If( Month(Now()) < 9, 1 ), 9, 1 ) ) )

Was this reply helpful? Yes No