Filtering Data By Date

[LaingBennett]

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. 

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

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

I am really stuck.

Regards

[RandyHayes]

@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.

[Krishna98]

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)
)
)

[LaingBennett]

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

[RandyHayes]

@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.

[LaingBennett]

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. 

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

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

[RandyHayes]

@LaingBennett 

So where does the chart play into this?

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

[LaingBennett]

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. 

[RandyHayes]

@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?

[LaingBennett]

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))