Determining reporting month

[JFurda]

I have a tough problem that I'm not sure how to solve. Our organization tracks the reporting month in an odd way (First Tuesday in the month until the day before the next month's first Tuesday). I built workflows that determine the current and previous month inside of several paths and appends the month start and month end to the data in that reporting period's stream. All is well and good with that, but the ask has grown. 

I now need to show quantities of actions and failures for a larger scope of time and generate these figures on a weekly basis. Because some of our actions can be backdated, previous month's data can change. Sounds fun, right? 

So, I built a flow to determine the start and end of a reporting month and the data looks great, but what I need to do is to apply it to my main incoming data to assign which reporting month-year it belongs in. 

For example, an action happens on 2022-05-01. It belongs to April's reporting window because 2022-05-03 is the first Tuesday of the month. 

I'd love to have a way of taking the output from one stream (the year-month, first Tuesday, and last Monday) and use it as criteria against the action date in the main datastream to determine reporting month. 
Action date: 2022-12-03 is between 2022-11-01 and 2022-12-05, to the reporting month is 2022-11. 

Any thoughts on how I can accomplish this? How can I use the First and Last days of a reporting period to assign reporting month to my main datastream? Should I build these validations inline with my other datastream?  I've attached my flow created to determine reporting month start and end from May of 2020 through April of 2030. 

[Luke_C]

Hi @JFurda 

Maybe something like this? Append the ranges to your action dates and then filter to the records that are relevant:

[SPetrie]

You could also accomplish this purely with a formula tool, but the formulas are admittedly ugly.

Instead of calculating out all the Tuesdays for a given range, it calculates first Tuesday for the month of the date provided and the following month. Subtracting a day gives you the last Monday of your desired range. Then you test if the target date is within the range, before the range, or after it to determine the reporting month.

[AdamSt · Accepted solution]

Hi @JFurda 

If your final goal is to determine the reporting year/month based upon the first Tuesday of each month to the first Monday of the next month as considered one month then you could do something like is shown in the attached workflow.  The main driver of this logic is that each Reporting_Date ends up falling into the month/year of the previous Tuesday, unless the Reporting_Date is itself a Tuesday in which case we just use the month/year of that date without modifying it.  This method does not rely on creating any lists for all the date ranges and should work for any Reporting_Date value.

[JFurda]

This worked incredibly well and is not a huge addition to the existing workflow. Thank your for the solution!