Skip to main content

How to Find Conditional Averages by Week

One interesting piece of analysis is to look at your KPIs normalized over time.

For instance, its important to know your sales in the last week, prior week, last 4 weeks.. but we wouldn’t be able to compare the results from the last 4 weeks to the last week.

But we could if we compared results all averaged to the week level of detail. Below we see results in the last 4 weeks of Superstore. We see that Sales in “TW” (This Week) were not only great, but great even compared to average performance in the last month or last 3 months.


Here’s how I did this:


Step 1: Find the last date in the dataset


Step 2: Find the last full week in the dataset

Also remember that the values you get from this calculation are driven by your configuration on which day is the start of the week. This gives me a start for Monday.


Step 3: Set up your various measure partitions by date.

Here I’m looking for dates that are 1 week in difference from my last date. Therefore, I would find the Last Week.

Here I’m looking for dates that are between 13 weeks in difference from my last date and greater than 1 week in difference. Therefore, I would find the Last 13 weeks.


Step 4: Create headers for your values (coming up soon)

This allows me to bin my values.


Step 5: Create denominators for your averages

This calculation is where the conditional average comes into play. Since we know the headers represent a certain amount of time, we can hardcode the number of weeks the value will be divisible by.


Step 6: Create your conditional sales metric.

We’ve already done a lot of legwork prior to this to line up the correct dates in the headers – so the sales values will naturally fall into the boolean statements we’ve already created. Same for the denominator values.


Its a way of looking at the business I hadn’t yet seen before but great usage of adding numbers to context. Hope you’ll get to use it soon!


Leave a Reply

Your email address will not be published. Required fields are marked *