cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GregShanks
Frequent Visitor

Filter to the Max Quarter for each Item

Hi,

 

I'm trying to filter to show the row for each Item where the Quarter is the most recent. 

 

ItemIdItemPricePriceIdQuarterQuarterIdServiceNameServiceId
1Item 1261Q1 20141Service Name 120
2Item 227.541Q1 20141Service Name 221
60Item 36.7564Q1 20141Service Name 343
60Item 32.25548Q2 201739Service Name 343
60Item 36.75550Q3 201740Service Name 343
61Item 4187.0571Q1 20141Service Name 416
62Item 51.1431Q1 20141Service Name 416
64Item 66.8811Q1 20141Service Name 542
64Item 62.29546Q2 201739Service Name 542
64Item 66.88581Q1 201842Service Name 542
137Item 740192Q1 20155Service Name 615
137Item 750551Q1 201842Service Name 615
138Item 825.5191Q1 20155Service Name 757
684Item 96.88596Q3 201948Service Name 821


Any suggestions would be much appreciated.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisPiasecki
Super User
Super User

Hi @GregShanks,

 

You can achieve this through a Group By on your Item ID and using Max on the Quarter. A similar example is shown below.

 

https://powerusers.microsoft.com/t5/Building-Power-Apps/Power-Apps-Select-Max-Row-for-Each-Group/m-p...

 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

View solution in original post

2 REPLIES 2
ChrisPiasecki
Super User
Super User

Hi @GregShanks,

 

You can achieve this through a Group By on your Item ID and using Max on the Quarter. A similar example is shown below.

 

https://powerusers.microsoft.com/t5/Building-Power-Apps/Power-Apps-Select-Max-Row-for-Each-Group/m-p...

 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

View solution in original post

Thanks. That's perfect. This is where I ended up with myTable being my original table.

//*Group item by maximum quarter
ClearCollect(
    colGroupByItem,
    AddColumns(
        GroupBy(myTable,"SearchingTypeId","ResultTable"),
        "MaxFiscal", Max(ResultTable,QuarterId),
        "Item", LookUp(ResultTable,QuarterId=Max(ResultTable,QuarterId),Item),
        "Quarter", LookUp(ResultTable,QuarterId=Max(ResultTable,QuarterId),Quarter),
        "ServiceName", LookUp(ResultTable,QuarterId=Max(ResultTable,QuarterId),ServiceName),
        "ServiceId", LookUp(ResultTable,QuarterId=Max(ResultTable,QuarterId),ServiceId),
        "Price", LookUp(ResultTable,QuarterId=Max(ResultTable,QuarterId),Price)           
    )
);


Cheers!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

Top Solution Authors
Top Kudoed Authors
Users online (90,078)