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]

HI @caslasgee ,

Try this

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

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[caslasgee]

Hi @WarrenBelz , Thanks for your response but it showed a blank gallery after putting the functions in the gallery items. Again, my labels in the gallery all stopped displaying. See below screenshot. Thanks.

[WarrenBelz]

@caslasgee ,

The code is correct based on the values you have posted. Does the code have any errors and can you please post your exact code use (in Text) used for the Items of the Gallery

[caslasgee]

Ok @WarrenBelz . No, It doesn't have any errors. The code ran but it wouldn't show anything in my gallery. eg, When i use ThisItem.Title do display the ListA title and others showed blank.

Below is the exact code. I replaced the ListA with Unit_JRP_Funding, ListB with Unit_FisicalYear and SS with Support Staff & Office.

With( { wData: AddColumns(Unit_JRP_Funding, "FYear", Year( LookUp( Unit_FisicalYear, Uid = ID ).FisicalYear ) ) }, Filter( wData, !("Support Staff & Office" in Title) && FYear = Value(drpfyear.Selected.Value) ) )

//Filter(Unit_JRP_Funding,!("Support Staff & Office" in Title),drpfyear.Selected.Value)

[caslasgee]

On the label to view the amount i did this

Text(Sum(Filter(ListB,Uid=ThisItem.ID),JRPNewAmount)

And i was able to view the amount of each title using my approach. Is it possible apply the filter on the label by modifying the above code to filter based on the Drop down (Year) that i already added. Such that when i select a Year from the drop Down, it only displays the year amount instead of summing all the values of the titles (A, = all the years). Something like (Though it didn't work)

Text(Sum(Filter(ListB,Uid=ThisItem.ID,Uid = drpfyear.Selected.Value),JRPNewAmount), "$#,##0", "en-US")

Just an idea though.

Thanks.

[WarrenBelz]

@caslasgee ,

Your individual amounts would simply be

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

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[caslasgee]

@WarrenBelz, The item gallery is this

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

Not sure if it will be possible to just view an item on a gallery that is not part of a List without using a LookUp/Filter. Your solution above belongs to another List (UnitB) but is which has a connection with UnitA (Uid).

Thanks.

[WarrenBelz]

@caslasgee ,

Sorry, a bit lost here - the code I supplied for the label was for a record based on the gallery Items I posted. Is this another matter apart from your posted question ?

[caslasgee]

@WarrenBelz, It is still on the same matter. On my initial question, I posted this

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

Where the List name is Unit_JRP_Funding (The real code). This was a mistake on my part. It was meant to be

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

But in any case, I replaced/Substituted all the codes accordingly and it still didn't work.

Thanks.