Sort Dropdown Filter to Show Latest Month and Year based on a LogDate Column

(0) Share

Report

Posted on by sc0rpiongirl

112

Hi community,

I have searched and found some similar cased in the forum and tried to get it worked myself but so far not successful. Hopefully someone could help me. I have a Date column in the SP Lists, and I created two calculated column to extract Year and Month from that Date Column (named LogDate). In PowerApps, I want to have two drop down on screen for user to select which month/year data they prefer to see and by default, the gallery should filter out the latest LogDate's Month and Year related data. For example, if the latest entry are 2/25/2023, by default, the gallery will filter data that are from Feb and 2023 (which are the default value I'd like to have shown in the dropdown when user arrive this screen).

Currently I have in Month "Items" property = Distinct('SPList', Month) but I couldn't figure out how to get it to show latest Month as default and force the gallery to filter that latest month data. (Same goes with Year but I figure it is the same solution as to the Month dropdown.

Please can anyone help. Many thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (0)

WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report
Hi @sc0rpiongirl ,

To get you started - Items of your Month drop-down

Sort( ForAll( Sequence(12), { MonthNo: Value, MonthName: Text( DateValue(Text(Value) & "/01/2023"), "mmmm" ) } ), MonthNo )

DefaultSelectedItems for current month

{ MonthName: Text( DateValue(Text(Month(Now())) & "/01/2023"), "mmmm" ), MonthNo: Month(Now()) }

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

Was this reply helpful? Yes No
sc0rpiongirl 112 on at

Like (0)

Report

Thank you Warren for the direction. I actually have came across many helpful tips on posts in which you helped a lot ppl like myself and your name (and avatar) became very familiar to me, and I know you are an expert user. The thing is I am rather new to Power Apps and trying to learn my way around. So if you were to treat me as the newbie I am, please could you be more clear of the direction. I tried to work out the logic from the formula you provided, I imagine I should replace Value with my data column? The fact is I am not able to get it work yet so would very much appreciate your hand-holding on this.

Many thanks!
S

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report

Hi @sc0rpiongirl ,

The Items and DefaultSelectedItems I posted were to be used unmodified in your Combo Box and would present a list of months to select in Calendar order and will Default the combo box to the current month (as you requested), however if you also want to display any existing data, it would need to match this structure.

The other thing you need to do is forget about those SharePoint calculated columns in Power Apps - they simply do not work reliably - use AddColumns() in your Filter. Before I do anything more here, I need to know the Items of your gallery presently as well as the content of your month and year fields (are they month names).

This is going to be difficult unless you can run with my suggestions and provide the code you have attempted.

Was this reply helpful? Yes No
sc0rpiongirl 112 on at

Like (0)

Report

Thank you for the prompt answer Warren. Much appreciated!

My SP Lists contains list of issues being monitored and reported every quarter but per request, in the fashion of Months eg. March, May, September, and December of every year (Not Q1/Q2/Q3/Q4). Some items might continue to exist the following reporting month and some would drop off. Originally I mirrored the format of the data in the excel file altho in Lists I used single line text column to house Month and Year, but later I realized I might need a date column in order to get the PowerBI visual as well as certain functions in PowerApps to work properly, therefore, I searched for some examples and thought adding a date column in Lists and using 1st date of that month where data is required then extract month and year out to auto-populate in the Month and Year column in the lists might work out. That's where I had the calculated column set up (extract Month and Year data to the Lists). Now from what you implied, I supposed I might have picked the ineffective approach...?

The bottom line is, I'd need Month and Year to work properly in both PowerBI visual as well as PowerApps to offer the capability of default filtering the most recent Month and Year data when loaded. Whatever is the most appropriate way of setting them up in order to make them work would be my end goal. To that end, I greatly appreciate your direction and education. And many thanks again for your generously offering your time and knowledge to a newbie :).

Was this reply helpful? Yes No
sc0rpiongirl 112 on at

Like (0)

Report

I should also added that the gallery shows items by Title, and related Month, Year as well as a brief description, of which by clicking each item should direct user to the screen where they could view the details of that specific issue. I've got everything work so far (it would appear) except I tried and failed to get the dropdown box which I set up to filter Month and Year on the gallery screen to work properly. Since there are over a dozen each reporting month (4 month/year), the idea is to default filter the latest month and year data when user are landed on the gallery screen, same ask for the Power BI visual, of Month and Year default filter.

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report

Hi @sc0rpiongirl ,

Your original question was Sort Dropdown Filter to Show Latest Month and Year based on a LogDate Column and I tackled that hoping to get you on your way. You still have not supplied any code you have attempted on the journey I am trying to guide you on - to start use AddColumns() instead of the calculated fields and have a look at how to match the month column formats, but your quarters are new pieces of information. I am away from home presently with limited connectivity and resources, so may not be able to help you adequately other than trying to correct code.

Was this reply helpful? Yes No
sc0rpiongirl 112 on at

Like (0)

Report

Apologies for the "expanding of scope" of my question - I had mentioned my code on my first post which merely filter the Month (and Year) data by using Items = Distinct('ListName'.Month) in gallery dropdown but recently was asked if I could default filter to most recent reporting month so I tried different coding which all draw errors, which made me wonder if I didn't set up Month and Year column appropriately - from what you suggested i.e. using AddColumn(), I might have and would need to steer to another direction by setting up the Month and Year data differently. I thought providing you a full picture of the data set up might help you see my logic of setting Month and Year by extracting them from date column, but it might have also "muddy the pool". I will look into approaches in similar cases by using AddColumn() and give it a try.

Thank you again for your direction. 🙂

Was this reply helpful? Yes No
sc0rpiongirl 112 on at

Like (0)

Report

Sorry for troubling you a bit more...If I might pick you brain on this part specifically - as I took a closer look at the codes you provided me, it would have worked if my data covers 12 month / year, but since my data only has 4 months (March, June, September and December) for every year, using conventional way of drawing Now() would not have worked IMHO, because as you see, we are in Feb. 2023 but my latest reporting Month is Dec. 2022 so the default should filter in Month dropdown "December", and Year dropdown "2022".

If I were to use the code you provided earlier in the ddMonth (dropdown box) "Items" and "Default" properties respectively, it shows "January" in Month dropdown box. Is there a way to set up the code in which it will pick up the distinct Month/Year value then filter out the latest as I mentioned above? Logically it'd be tricky using -1 or -2 along with Now() because you see we could be in Jan. 2023 or Feb. 2023 or even Mar. 2023 now but as long as March 2023 issues haven't been logged, when I check the data in app, the gallery should still default filter December 2022 data for me because for the dataset, the latest available data are still logged during Dec. 2022, as soon as someone log March 2023 data, the filter default will then show March 2023.

I am most grateful for your time.

Was this reply helpful? Yes No
saber17 2 on at

Like (0)

Report

Hi @WarrenBelz this worked as a charm for me however i want to extract and show only day out of my date and show this in my combobox the similar way you did for month i want to do this for day how can i achieve this

Was this reply helpful? Yes No
WarrenBelz 153,026 Most Valuable Professional on at

Like (0)

Report

HI @saber17 ,

Can you please post a new thread on this and include the code (in Text) you have used and the current and desired outcome.

Was this reply helpful? Yes No