Filter by Year and Sum Values

[caslasgee]

@LaurensM. I tagged you because you proffered solution to a similar question Weeks ago.

Here is my scenario, I have 2 SharePoint list as shown below.

 I have a gallery(Gallery4) that displays ListA items. I have also used the clearcollect function to populate the list of Year in a dropdown using the below;

ClearCollect(JRPFYear,ListB)

And i have populated my Dropdown (drpfyear) using the below to display the years.

Distinct(JRPFYear,Year(FisicalYear))

 On my gallery (Gallery4), I have applied the below function on the items field

Filter(Unit_JRP_Funding,!("SS" in Title),drpfyear.Selected.Value)

I have added this in the gallery but it shows the total amount in respective of the selected date as shown below.

What i want is whenever a year is selected from the dropdown, It should display only the value for that year as show above.

Thanks.

[WarrenBelz]

@caslasgee ,

Sorry, but I am further unclear here - I based my code on your requirement below

which hopefully it should achieve (as well as the Label value I posted). 

[caslasgee]

HI @WarrenBelz , Apologies, I think i got misunderstood your previous response. The initial code you posted was this

With(
   {
      wData:
      AddColumns(
         ListA,
         "FYear",
         Year(
            LookUp(
               ListB,
               Uid = ID
            ).FiscalYear
         )
      )
   },
   Filter(
      wData,
      !("SS" in Title) && 
      FYear = Value(drpYear.Selected.Value)
   )
)

And this should be put in the label within the gallery right?

Again, You requested that i also share my Gallery Item code :

Filter(ListA,!("SS" in Title))

So at the end, The error message seen is "Expected Text Value".

Thanks.

[WarrenBelz]

@caslasgee ,

No the first part is the Items of the Gallery (replacing your current Items), which then should support a Label of

Text(ThisItem.JRPNewAmount, "$#,##0", "en-US")

[caslasgee]

Hi @WarrenBelz There was no error in the code inserted in the Gallery. But if i try putting a Label text, It say JRPNewAmount isnt recognized. See error screen below

What i observed is that even though the labels are not working, It fetches information of ListA only.

Thanks.

[WarrenBelz]

@caslasgee ,

Please see amended original post

[caslasgee]

Hi @WarrenBelz , I tried this modified formula and it still didn't work.

This time around, Its not picking the Filter function and it says Invalid Number of Arguments, Received 1 expected 2.

Thanks.

[WarrenBelz]

@caslasgee ,

Just a bracket (I am free-typing code here) - now fixed.

[caslasgee]

Hi @WarrenBelz , Thanks for your effort so far, But unfortunatley, I still get the error. Codes showed without any error but cant add any label to the Gallery.

Thanks.

[WarrenBelz]

Hi @caslasgee  ,

If you use the code I posted, you should have the fields: -

List A - Title and ID

List B (matching) - FYear (the Year from FiscalYear) and JPRNewAmount

These are then filtered for everything without SS in the Title field and have the FYear matching the year selected in the dropdown. 

So, if you put Text(ThisItem.JRPNewAmount, "$#,##0", "en-US") in a Label, it should show the value of this assuming it is a number field (please confirm this). Also, you need to tell me exactly what the error is for me to help you. 

[caslasgee]

Hi @WarrenBelz , Again, Thanks.
All the formulas you have mentioned actually showed up, But it still shows error within the gallery. See the screenshot below.