# Time Comparisons In Power BI: This Year vs Last Year

I want to go over how you can easily do time comparisons in Power BI and specifically calculate this year versus last year. I’m going to show you a couple of techniques that you can utilize for time comparisons like this. You can watch the full video of this tutorial at the bottom of this blog.

These are some of the easiest things that you can do in Power BI. They are also some of the first examples that you should work through as you’re learning how to operate DAX effectively, especially on the Power BI desktop.

First, just let’s just do a quick recap of how you should set up your models.

This is the waterfall technique that I talk a lot about. Others call it a snowflake, but I like this concept of the waterfall and its filters flowing down.

Your Date table is absolutely key here. You do not want to do time intelligence time comparisons or time comparisons without a Date table. You have to have a good Date table. If you want to learn how to set up one really effectively and quickly, definitely check out a lot of Enterprise DNA content around Date tables.

## Time Comparisons Using SAMEPERIODLASTYEAR

To begin with, I’m going to a table here so that we can review the numbers and I’ve got my slicer selected in 2016 here as well.

Instead of sales, I’m going to create another core measure, which is Total Quantity. I’m going to sum up the quantity column, which is in my Sales table.

I just think of this as a core measure, and then I can branch out into all of these other calculations, like time intelligence, moving averages, dynamic grouping, and other different patterns or formula combinations. It’s just reusing the patterns over and over again.

I’m going to drag my Total Quantity measure into the table so we can see the results. Now that I have this, I can quickly calculate my last year’s quantity.

Iâ€™ll go new measure and I’m going to call this Quantity LY (last year). This is where I can use a function called CALCULATE. This is the most important function in the DAX formula language. It enables you to change the context of a calculation. In this measure, I still want to calculate that total quantity, but I want to do it in a previous timeframe.

In this first example, I’m going to show you this simple function called SAMEPERIODLASTYEAR. It does exactly what it says. It returns a set of dates and the current selection from the previous year.

So basically by putting this inside CALCULATE, I’m able to bring my quantity from one timeframe into another timeframe. And the SAMEPERIODLASTYEAR function allows me to do it exactly with one year difference. I’m going to show you a better combination to use, but I just showed you this one because I don’t want you to get too confused.

Now, I’ll drag Quantity LY and you see that weâ€™re basically comparing the quantity sold this year on the 1st of January 2016 to what I sold last year, 1st of January 2015.

So, if I click on 2015 on the slicer, you’ll see that this first number should be 115. And from there, we can now run time comparisons.

## Using Measure Branching Technique

We can actually work out the difference of this year versus last year. And so from that, I can say Quantity Diff YoY (difference year on year). And then all I need to do is subtract Quantity LY from Total Quantity. I can just reference my measures within a measure. This is called measure branching.

And now when I drag this measure in, you can see that this calculation has been dynamically generated from these two measures, Total Quantity and Quantity LY. There’s nothing hardcoded because when I click on 2017, I’m going to see the difference. There was nothing done in 2014.

So in this data set, which is a generic old data set, I can see the difference. And we can turn that into a visualization quite easily – now I can get the quantity difference on a daily basis.

We might want to filter this down a bit more. Maybe we’ll create another slicer here that enables us to select a specific month, so we can see on a monthly basis.

And remember, you can also change the context within here. If I wanted to not select anything there, I can actually see the monthly difference very easily without having to change any of my calculations.

So now I’ve showed you how to use the SAMEPERIODLASTYEAR function. There is also a function called DATEADD, which enables you to do this as well. I prefer DATEADD because it is more versatile.

And for this, I want to jump quickly to the analyst topic case. So the Analyst Hub is Enterprise DNA’s web-based application that supports your Power BI development. Inside there is a range of different apps and I’ve already embedded my DATEADD formula pattern in here.

Instead of me writing it out, I’m just going to come in here, search for my formula (sales last year), and then copy it.

Then, Iâ€™ll bring it into my model, go new measure and create another name, as we canâ€™t use the same. I’m going to call this Quantity Last Year. All I need to do is change the parameters here. Instead of Total Sales, I’m going to place Total Quantity.

And then just like that, I have this new Quantity Last Year calculation, which is basically going to return exactly the same number.

It’s going to return exactly the same number here, there is literally no difference between this calculation here and the SAMEPERIODLASTYEAR function. But the benefit of using DATEADD is the versatility.

## Conclusion

In this blog tutorial, I showed you a couple of ways to calculate this year versus last year. You can use the SAMEPERIODLASTYEAR, but I highly recommend the DATEADD function for time comparisons. We have a lot of content about this function on Enterprise DNA, so definitely check them out.

I also recommend that you use the Analyst Hub. You can save all your patterns there and bring them into your model.

Start using these techniques, including the waterfall model set up. These are the first things that you should be exploring within Power BI from a calculation point of view.

Once you learn how to do this, you can quickly do interesting analyses, especially with all the additional filters that you can place on your data when you build an optimized data model in the background.

Good luck with this one. All the best!

Sam

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## Building Your Data Model Relationships In Power BI

In today's tutorial, I want to highlight how important it is to build your data model relationships...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## Discover Unique Insights Using Power BI TOPN Function

In this video, I wanted to find an insight that was really unique, and hugely valuable for business....

## A New Look At CALCULATE – Power BI

In this tutorial, we'll be looking at how to use the DAX function CALCULATE in Power BI. You can watch...

## Ranking Visualization In Power BI – Dynamic Visual

In this blog post, I want to teach you how to show your customers moving through groups over time in a...

## Ranking Insights Using TOPN In Power BI

In this article, we will explore how we can use the TOPN function in Power BI. You will really enjoy...

## Data Storytelling And Important Questions To Ask

When it comes to data storytelling, there are three important factors to consider to make sure that our...

Many times when you get into more advanced formula with DAX, you'll find that the totals that show up...

## Query Editor In Power BI | An Overview

In this tutorial, weâ€™ll show a detailed example of why we always need to use the Query Editor in Power...

## Utilizing Measures And Visualizations In Power BI

In this tutorial, you'll learn about the essential things that need to be prepared in making a...

## Power BI Feature Bookmark Navigator: Tutorial & Review

As part of the November 2021 Power BI updates, Microsoft included a new Power BI feature called...

## Adding Workday And Weekend Numbers Into Your Date Table: Time Intelligence Technique In Power BI

This is a very unique example of Time Intelligence in Power BI, especially when working with workdays...