Dynamically comparing current totals to last year totals

[EnterpriseDNA]

One type of analysis I like to use quite often is comparing this year totals to last year totals. I want to also always make it as dynamic as possible. All I should have to do is change the context of my calculation (ie. bring in a new dimension like regions/products etc) and everything should automatically re-calculate.

I want to be able to select any time frame I want and then to be quickly shown the total in the current filter context (or current selected context) versus the exact same time frame the year before.

This is the end result we're aiming for with this example.

Once you understand how to complete this in Power BI, you'll be amazed at how easy it is to complete this analysis versus using tools of old.

There are a few steps to creating this analysis, but once tied in altogether you have created a pattern of DAX code/coding that is re-usable across numerous scenarios.

First, we want to create a total of a column or any aggregated calculation for that matter. Use aggregation functions like SUM, AVERAGE, COUNTROWS or iterating functions like SUMX, AVERAGEX etc.

Let's run with a simple example of total sales.

Firstly lets write a total sales function. This is easy.

Now we have our total that when placed against a date context sums up the total sales for that date.

 Let's now calculate this total for the same period last year.

Thanks to the built-in time intelligence function for DAX we have a function called SAMEPERIODLASTYEAR. When used inside a CALCULATE statement it changes the 'context' of the calculation to the exact same period but one year before.

What's great about this is we can still change other key dimensions in our analysis and the information will change for the context it is updated to. Examples could include Regions/Products/Customers.

Okay so now we have created these measures which when sitting side by side compare this year to last year.

For me though, I want something more. I want to show this cumulatively. I want to be able to dynamically select any time frame and see the cumulative difference between this year to last year.

Let's work through how to do this.

First we need to create the cumulative totals for this years current sales but only for the current time frame selected. For this we will need to use the FILTER & ALLSELECTED functions, all within the CALCULATE statement.

See here how this is done.

As you can see we have input our total sales measure inside the cumulative total pattern for the time frame which is selected in the report.

We are almost there. Finally, we can now create a cumulative total for the same period last year. It's remarkably easy. All you have to do in this case is sub in the total sales LY measure into the cumulative total pattern.

Now we can get some insanely good analysis so easily. We can slice across any of our other dimensions, and this calculation will automatically update and show us how performance was versus last year.

I use this technique quite often and is a simple pattern of analysis that will really showcase your skills. Good luck with it!

Download finished demo file below.

Sam McKay, CFA
Enterprise DNA

Enterprise Power BI (LinkedIn group)

This Year Total vs Last Year - Dynamic.pbix

[osoosh]

A very useful thought for doing analysis to pick up trends from previous years. What will be your approach to dig deeper towards day of sales (eg. Monday, Tuesday, etc) since sameperiodlastyear only works with contiguos date selections?

[EnterpriseDNA]

I think there is lots you could do. Plenty around how you set up the slicers and filters on a particular page. Maybe I'll dive deeper in an upcoming post. Thanks!