How to determine, Is [Date] from the Current, Prior or Older Month?

[SA-KRP]

New to Power Automate and struggling with learning new limitations, ways to accomplish things.

In the SPD days, I solved this with a simple calculated (Text) field which resulted with a value of "Current", "Prior" OR "Older" value, which could then be used in to filter items. I then had a WF that would run on the 1st of the next 2 months that would trigger an update on the item so that the Calculated column would update. Not sure this was the best way, but it was easy to setup and worked as needed, it confirmed year & month in the calculation.

"=IF(DATE(YEAR([Start Date]),MONTH([Start Date]),1)=DATE(YEAR(Today),MONTH(Today),1),"Current",IF(DATE(YEAR([Start Date]),MONTH([Start Date]),1)=DATE(YEAR(Today),MONTH(Today)-1,1),"Prior","Older"))"

With Power Automate, I read several people trying to solve the "update calculated field" and it was recommended to replace the calc field with a standard field. I used a text field.

Goal;

In a simple time entry list, I want to be able to report/display/filter on items in 3 views the 'Current', 'Prior' or 'Older' (all other months) months.

Date columns used: [StartDate], [EndDate]

I thought maybe using an expression to convert utcNow & [StartDate] into integers for yyyy & mm but then wasn't sure how to handle the 3 outcomes since the Condition only has 2 options 'true' OR 'false'.

Hoping someone can point me in the right direction on a good way to solve this issue.

[ecflyer]

I used a calculated column in the list for 'Start of Month' =DATE(YEAR([Start Date/Time]),MONTH([Start Date/Time]),1) and one for 'End of Month' =DATE(YEAR([Start Date/Time]),MONTH([Start Date/Time])+1,1)-1. And then used the following filters to get items for the current month.

I also have views set up for showing items today to month end as well as this month plus future. I'm sure Prior and older filters would be similar.

[SA-KRP]

Thanks ecflyer, I am reading your filters above and I can't visualize how this will group say all of Jun 2024 entries, in a separate grouping all of May 2024 and finally a third grouping of all entries from Apr 2024 and older.

I'll assume this will work for the initial filtering, but won't I be in the same place as before, a calculated column that will only update if the item is modified?

I also found this referring to the Calc column

Calculated column values are calculated & updated only when:

• A new item is created
• An existing item is updated
• Calculated column formula is updated

The issue I am trying to resolve is not the accuracy of the calculated column (my above formula works as expected), but rather the best way in SPO and Power Automate to force an update only on items less than 3 months old so the calculated column updates on the 1st of every month.

It was simple in SPD 2010 workflows but with the 28 day flow limit it seems the only way would be to use a schedule trigger have it run daily, along with Trigger Filters & GET Filter Queries to limit which items are affected.
Where I was getting stuck was trying to find a condition and format date functions to determine which items needed updating and when.

I'll keep working on that but thought, there must be a better way, but maybe that's how it has to be done?

[SA-KRP · Accepted solution]

I resolved this problem by;

1. Using a daily Recurrence trigger set to only run on the 1st day of each month with a trigger condition of "@equals(dayOfMonth(utcNow()),1)"
2. Initialized a variable "varCounter" to be used to retrieve the value of the item's [Counter] number field
3. Then a Get Items Filter Query = "Counter lt 3" so only the items that have a [Counter] > 3 will be processed
4. With a "Apply to Each" set the varCounter to the items [Counter] field value then incremented it by 1
5. Then Updated the same item's [Counter] field with the new value to force a refresh of the calculated field.

Testing seems to be working, we'll see when the month rolls over.