Calculating percent change compared to last year

[ytc-reports]

Hi Guys.

I have a table (called SALES) with 3 columns

Date | Store | Revenue

Each row is an single day at an individual store.

I'm trying to do the following things:

Display a graph, on a monthly level, of a particular store's earnings, compared to previous years on the same graph.

(This job I've done in a very sloppy way - by making a different column for each years sales figures, and graphing those different columns - I dont think I should do it that way).

Calculate, for each store and for each month, a *percent change* from the same month last year.

Calculate, across all stores for each month, a percent change from the same month last year.

I have a way to calculate an individual store's sales for each month, but that's about it. I've got this column (not measure):

StoreMonthSales = CALCULATE(sum(SALES[Revenue]), ALLEXCEPT(SALES, SALES[STORE], SALES[MONTH], SALES[YEAR])) 

which uses columns I made to pull out the month and year from the 'date' column. I feel like even that step is wrong though. Can I get some advice?

[DoubleJ · Accepted solution]

Hi

When you do date calculations it's best to have a calendar table. You can easily create one with the "New Table" option and this formula (you might have to replace the semicolons with commas):

Calendar = CALENDAR(DATE(2014;1;1);DATE(2017;12;31)) 

Link the calendar table to your Sales table.

Then you can create these measures:

Revenue = SUM(Sales,Revenue)

RevenuePY =
CALCULATE (
     [Revenue],
     SAMEPERIODLASTYEAR(Calendar[Date])
)

RevenuePYDiff = 
[Revenue] - [RevenuePY]

RevenuePYDiff% = 
DIVIDE(
     [RevenuePYDiff],
     [RevenuePY],
     BLANK()
)

That should also work when you use the stores in your visual.

By the way, a very nice explanation how this works - inclusive YTD calculations - can be found here: Power BI YTD Calculations

I hope this helps!

JJ

[Lifan]

Brilliant solution! I have changed the dates to 1st of each month for easier comparing month to month, but because the date is not continuous, it gave me an error. Your idea is the last piece of puzzle! thanks!

[ytc-reports]

THANKS! Never seen that Calendar trick before. Very helpful.

So far it seems like it's giving me what I want.