Filter Gallery by Date Range, then Change Date Range

[ericonline]

Hello, 

I'm looking a starting point for a search query for the following scenario: 

• PowerApps Gallery with Sharepoint List as a Data Source
• Need to pull all the rows added between 1/1/18 and 6/30/18 that have <employee_id> column in common
• The challenge is, after 6/30/18, I need a way for the query to then only pull rows added between 7/1/18 to 12/31/18 that have <employee_id> column in common
• This cycle will repeat year in, year out

1. What is a good PowerApps query to get me started

2. How can I make the query adaptable? Basically, on 7/1/18 the query should return 0 results until new records start coming in. 

Thank you very much, 

[Anonymous · Accepted solution]

If i understand correctly, you want o split the year into 2 halves, and only report on the sales up to today in the half of the year that you are currently in.

You could create a new column in your share point list that is either "1" or "2"

Use a formula like this in the sharepoint column:

If(or((text(DATE,"mm")="01",text(DATE,"mm")="02",text(DATE,"mm")="03",text(DATE,"mm")="04",text(DATE,"mm")="05",text(DATE,"mm"))="06","1","2")

Then in powerapps you can filter by "1" or "2", the current year, and employee id.

[ericonline · Accepted solution]

This is a great lead, thank you @Anonymous. Since this is my first time using a Calculated Sharepoint column, I need a little more clarification if you can. 

I'll add a "period" column to the Sharepoint list to represent which period of the year (1st or 2nd) the record was submitted. The formula will be: 

If(or((text(DATE,"mm")="01",text(DATE,"mm")="02",text(DATE,"mm")="03",text(DATE,"mm")="04",text(DATE,"mm")="05",text(DATE,"mm"))="06","1","2")

I'm having trouble understanding which parts of your formula are the columns to look at. Is "text" or DATE the column names to evaluate in the formula?

Thanks again!

[ericonline]

@Anonymous, figured it out! There was an extra parenthesis in your original formula.

Here is the sharepoint formula that I used:

=IF(
OR(
TEXT([date],"mm")="01",
TEXT([date],"mm")="02",
TEXT([date],"mm")="03",
TEXT([date],"mm")="04",
TEXT([date],"mm")="05",
TEXT([date],"mm")="06"),"1","2")

Now I have my "period" column... time to try combining this with the [employee_number] column to get the correct results. Thanks again for your insights. 

[DavesTechTips]

Hello everyone,

This might not be entirely related, but also queries to date columns in SP are not delegatable (nor is calculated columns in SP), so if you have to use SharePoint for the data, the other option would be to use Flow to fetch the data and return it to PowerApps. Flow can use ODATA queries to get to the correct data and then possibly manipulate or join the data with other services before returning it to PowerApps. I love this approach and it has come in extremely handy in the past. Some might say that using a flow for retrieving data might feel 'expensive' but it does allow for many different functional and security options going forward.

See below demo what can be achieved with this. Please see below and let us know if you have any questions or suggestions:

https://youtu.be/m_j_xN7-LjU

Twitter: @DavesTechTips

YouTube: https://www.youtube.com/user/dawidvanheerden
**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.