Compare sales data to previous period.

[jwininger]

So I have 3 years worth sales data in the sales table.  I want to be able to compare the last 5 weeks and 13 weeks to the same period last year.  I have a calendar table that manages all my date info.  I can not figure out how to compare to the previous periods.  I have attached a sample of both my date and sales data tables.  

Thanks for the help.

[edhans · Accepted solution]

@jwininger Check out this article at SQLBI that is amost tailor written to your question. You aready have the TRUE/FALSE columns for the 13 and 5 week periods, and the article addresses both the current and prior year (same period last year) calculations.

[jwininger]

Thanks so much.  That article was exactly what I was looking for.

[Anonymous]

When you're looking at dates grouped into weeks you have to be careful because weeks can span months and years.  So this really only works if you're looking at the week-level in your data - it will return the same value for all days in a specific week if you're looking at a specific date (i.e. if 1/9 and 1/10 are in the same week you'll get the same value returned).

Here's one solution:

In your Date table create two new calculated columns:

YearKey = YEAR('Date'[Date])

Year Week = "Week " & WEEKNUM('Date'[Date]) & " " & FORMAT('Date'[Date], "yyyy")

YearWeekKey = ((YEAR('Date'[Date]) - MIN('Date'[YearKey])) * 52) + WEEKNUM('Date'[Date])

(an interesting side-effect of this is that it handles when weeks span years)

Then create the following calculated measures in your Sales table:

[Net Sales] = SUM([sold])

Sales P5W = CALCULATE([Net Sales], FILTER(ALL('Date'), 'Date'[YearWeekKey] <= MAX('Date'[YearWeekKey]) && 'Date'[YearWeekKey] > MAX('Date'[YearWeekKey]) - 5))

Sales P5W LY = CALCULATE(Sales[Sales P5W], SAMEPERIODLASTYEAR('Date'[Date]))

The SAMEPERIODLASTYEAR function is really handy for simply going back one year.

Again - test test and test against your data.  And make sure there's a relationship between the Sales and Date tables in your model.  I've tested this on my end and it seems to work with my data.

If you're using something like a 4-4-5 calendar instead of the traditional definition of a week you can find all sorts of great resources from the sqlbi.com guys at daxpatterns.com.  

https://www.daxpatterns.com/time-patterns/

https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/

Hope that helps.

Eric