How can I get the average of the last 3 months, excluding blanks, but still getting 3 data points?

Copper Contributor
Hi everyone! I appreciate any help you can offer. I’ll try to be concise:

I have a 3-column table. Month, Name, and Sales. Each row is how many $ in sales a salesperson got for that month.

It’s arranged like: first 1000 rows are all Jan/1, next 1000 all Feb/2, etc.

I need to get the average $ amount sold by each salesperson for last 3 months. But—-if one of those last 3 months is blank, I still need a 3-month average. It would need to skip over the blank and grab the next most recent month to total 3 data points.

Right now I have a very simple pivot table for this. I was filtering for the last 3 months and getting the average value there. This was working great for my purposes until I realized that salespeople didn’t always have data for each month, so some people’s averages were only from 1 or 2 results.

Is there any way (pivot table or not) that this can be done? There’s over 1000 salespeople, so manually is not feasible. I have lots of people without numbers for a month for whatever reason, so I need to make the calculation work around that.

Thanks for any help! Please let me know if I can include anything else or if it’s not clear.
1 Reply

@YaBoi1270 Since you didn't provide some data to play with, I created a small mock-up based on the situation you described and a fairly simple PowerQuery solution. If this doesn't make sense, perhaps you can upload an extract from your real file. Just make sure that you replace real names with fake ones.