Organize data by months, need the "months" to be from the 20 of first month to 19 of next

[RobMotiwalla]

Hey All,

I am working with some data that comes in about 5 times a month, and I need to take the average based on the month. All of that is straightforward, but I have one set in which I need to take the average of the dataset from the 20th of the previous month, to the 19th of the current month, inclusive (for April data I need to take the average of all the numbers from March 20th to April 19th, inclusive). The final problem I am running into with this is that those specific dates may not be in the data, so it could start on the 21st and end on the 18th, if there were no data points for the 20th and 19th.

I know I could do this slowly and manually with the formula tool, but I am definitely looking for something more simple and dynamic than that.

Here is the dataset, and I need to find the average for Jan - March

RecordID	Date	Regional avg
1	4/29/2023	15.25
2	4/21/2023	15.25
3	4/14/2023	15.25
4	4/7/2023	15
5	4/4/2023	15
6	3/31/2023	15
7	3/24/2023	14.88
8	3/17/2023	14.88
9	3/10/2023	14.75
10	3/3/2023	14.5
11	2/28/2023	14.25
12	2/24/2023	14.25
13	2/17/2023	13.75
14	2/10/2023	13.75
15	2/3/2023	13.75
16	1/31/2023	13
17	1/27/2023	13
18	1/20/2023	12.38
19	1/13/2023	12.38
20	1/6/2023	12.25
21	1/4/2023	11.75
22	12/31/2022	11.25
23	12/23/2022	11.25
24	12/16/2022	11.25

TIA!

-Rob

[tristank]

Hi @RobMotiwalla 

So when you say you need to find the average for Jan to March do you mean the averages for Jan 20 - Feb 20th and so on? With the exception that those exact dates may not always be available?

[RobMotiwalla]

@tristank 

That is close, it needs to be Jan 20 - Feb 19. Then Feb 20 - March 19. Then March 20 - April 19

[jfha97]

Hey @RobMotiwalla 

Not sure if this works, as I did not spend much time on it, but a workflow around this logic should work. I tried to get the min closest to the monthly start date and the max closest to the end date.

I set 20 as the floor for the start date and 19 as the ceiling for the end date.

Hopefully this can help/point you in the right direction.

Best,

jfha97

[smoosh · Accepted solution]

Attached is a way that should work for you. 

[RobMotiwalla]

Thank you, @smoosh and @jfha97 !!