Average in Reports to exclude current week

ginamt3

Hi, We have some KPIs that are measured on a weekly basis. I have a sheet that calculates weekly totals. When I pull a report to see weekly averages on a 4 week or 3month basis, Monday morning the totals are skewed because the totals for the current week hasn't accumulated yet. Is there something I can do in the report to not count the current week in the average?

Matt Lynn-PCG

@ginamt3 Since the report function doesn't have a current week filter you'd need to add a helper column on the source sheet(s). There could be several examples but one would be something like a hidden column named "Current Week". In that column you could put a formula such as =if(weeknumber(date@row)=weeknumber(Today()),"Yes","No")

This would put a yes on any row that the date reflected today's week. Then on your report you'd add a filter that said if the helper column equals "No" or didn't equal "Yes".

If you need it to span years and weeks (so that the 25 week of 2024 and 2025 etc didn't all get highlighted as a yes, you'd need to add the year to the formula. Something similar to:

=if(and(weeknumber(date@row)=weeknumber(Today()),year(date@row)=year(today())),"Yes","No")

Matt Lynn-PCG

@ginamt3 Since the report function doesn't have a current week filter you'd need to add a helper column on the source sheet(s). There could be several examples but one would be something like a hidden column named "Current Week". In that column you could put a formula such as =if(weeknumber(date@row)=weeknumber(Today()),"Yes","No")

This would put a yes on any row that the date reflected today's week. Then on your report you'd add a filter that said if the helper column equals "No" or didn't equal "Yes".

If you need it to span years and weeks (so that the 25 week of 2024 and 2025 etc didn't all get highlighted as a yes, you'd need to add the year to the formula. Something similar to:

=if(and(weeknumber(date@row)=weeknumber(Today()),year(date@row)=year(today())),"Yes","No")

ginamt3

Thank you! updated it and it worked like a charm.