cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Filtering Data By Date

Hi there everyone,

 

Struggling to find some existing help on this one in the community so here goes. 

 

I want to set a control on my page where the data number changes in the green box and the chart changes also. 

 

Screen Shot.jpg

This data is using a count of rows in the following table.

Master List.jpg

How on earth do I get the data to update in the way I want it to.

I am really stuck.

Regards

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Filtering Data By Date

@LaingBennett 

My bad...forgot something in the formula.  Change to this:

AddColumns(
    GroupBy(
        DropColumns(
            Filter(
                AddColumns('2020 - Valuation - Sales',
                    "createDate", Text(Created, "[$-en-GB]yyyymm"),
                    "postCode", 'Postcode Area'.Value
                ),
                IsBlank(Valuation_DD.Selected.filter) || StartsWith(createDate, Valuation_DD.Selected.filter)
            ),
            "createDate"
        ),
        "postCode", 
        "DATA"
    ),
    "PostcodeCount" , CountRows(DATA)
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

36 REPLIES 36
Highlighted
Super User III
Super User III

Re: Filtering Data By Date

@LaingBennett 

This is exactly what PowerApps was built to do!

For what you want, all of the formulas for your chart as well as your number label would all be based off of the values produced by the dropdown.

 

Start with that approach and post any issues you run into.

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Helper III
Helper III

Re: Filtering Data By Date

Hi @LaingBennett 

 

it was some tricky kind of stuff, what you can do is

 

on start of app do this => Set(vThisMon,Text(Today(),"[$-en-US]mmmm"));Set(vPrevMon,Text(DateAdd(Today(),-1,Months),"[$-en-US]mmmm"))

 

then in items of dropdown => ["This Month","Prev Month"]

 

then in items of barchart =>

 

If(
DD1.Selected.Value = "This Month",
AddColumns(
GroupBy(
Filter(
AddColumns(
listName,
"DateMonth",
Text(
DateColumnInList,
"[$-en-US]mmmm"
)
),
DateMonth = vThisMon
),
"PostCode",
"GPostCode"
),
"Count",
CountRows(ThisRecord.GPostCode)
),
AddColumns(
GroupBy(
Filter(
AddColumns(
listName,
"DateMonth",
Text(
DateColumnInList,
"[$-en-US]mmmm"
)
),
DateMonth = vPrevMon
),
"PostCode",
"GPostCode"
),
"Count",
CountRows(ThisRecord.GPostCode)
)
)

Highlighted
Helper II
Helper II

Re: Filtering Data By Date

Hi there,

 

Thank you for your help.

 

I have set to Dropdown List to the following:  ["THIS MONTH", "LAST MONTH", "THIS YEAR", "ALL"]

 

I am now having a little trouble with adapting the formula you gave the the names of the elements in my app. 

 

The data table is called - VALUATIONS

The drop down is called - PIPELINE_DD

 

Can you help me a little further?

 

Also will this code allow me the filter the data to view the current in the current year and all time? 

 

Regards

 

Highlighted
Super User III
Super User III

Re: Filtering Data By Date

@LaingBennett 

Here is what I would recommend then to solve this.

First, set your Items property of your DropDown to the following:

 

Table(
    {Display:"THIS MONTH", filter:Text(Now(), "[$-en-US]yyyymm")},
    {Display:"LAST MONTH", filter:Text(DateAdd(Now(), -1, Months), "[$-en-US]yyyymm")},
    {Display:"THIS YEAR", filter:Text(Now(), "[$-en-US]yyyy")},
    {Display:"ALL", filter:Blank()}
)

 

 

Next, use the following formula for your filter:

 

DropColumns(
    Filter(
        AddColumns(VALUATIONS,
            "createDate", Text(Created, "yyyymm")
        ),
        IsBlank(PIPELINE_DD.Selected.filter) || StartsWith(createDate, PIPELINE_DD.Selected.Filter)
    ),
    "createDate"
)
        

 

 

This should give you what you want very simply. 

 

EDIT: Oh, and your label for the count can just infer from the filter.  But, I would need to understand more about where you are using this filter - I am assuming in the chart, but wanted to be clear.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Helper II
Helper II

Re: Filtering Data By Date

Hi Randy

 

I have done what you are suggesting and not getting anything. Remember I am after a count of each post code area based on changing the Drop Down Menu. 

 

LaingBennett_0-1603116514809.png

The data I am using is coming from a google sheet. 

LaingBennett_1-1603116643984.png

The data comes from a Sharepoint list but I copy across the sheets using Power Automate as I could not get the Choices from Postcode area to show.

 

Regards

 

 

Highlighted
Super User III
Super User III

Re: Filtering Data By Date

@LaingBennett 

So where does the chart play into this?

And, where are you putting the filter formula I suggested (what control type and property)?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Helper II
Helper II

Re: Filtering Data By Date

Hi,

 

I want the Chart to update showing the count of each postcode areas based on the dropdown. 

 

I have put the formula you gave my in the chart items. 

Highlighted
Super User III
Super User III

Re: Filtering Data By Date

@LaingBennett 

My apology that I see now you are getting this from a google sheet and not SharePoint.  You have a Date column in that sheet that we need to account for.  Is the Date column coming through to your PowerApp as a Date type or a Text type?

 

What was your original formula on the Chart control Items?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Helper II
Helper II

Re: Filtering Data By Date

Hi there,

 

The column in Google Sheet for the Date is formatted as a Date. 

 

To get the count of the rows by postcode area without any filtering I am using.

 

AddColumns(GroupBy(Valuation, "Postcode_x0020_Area", "DATA") ,"PostcodeCount" , CountRows(DATA)) 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,731)