In this post, we will go through how to create a Power BI chart that compares numbers each year vs each previous year. We will use sales data as an example.
First, let’s connect to our data from Power BI Desktop. We will use the sample data from here and connect as in the example. This data holds 3 years of Orders data.
Once connected, create a simple date table that holds dates between the year ranges.
Now let’s build our report. First, a simple view of our data. We can see we have orders from 2017 to 2019:
Let’s create a measure called Total Sales. This displays the total of all sales based on the context:
Total Sales = SUM('Orders'[Amount])
Now let’s use SAMEPERIODLASTYEAR to create a measure for the total sales this year:
Total Sales Last Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date]))
If we swapped the day for YEAR-MONTH, we can see we are now comparing the YEAR-MONTH periods. For example, in January 2018 there was a total of 320 in sales. In January 2019, the total was 100 and Total Sales Last Year is displaying the 320 correctly:
From here, we can create our column chart showing the comparisons of year vs previous year:
As 2017 did not have previous year data (i.e. maybe it was the first year of business), we may want to exclude it. Let’s remove it by filtering the visual, and change the chart to a Line and Clustered Column Chart, which can also easily allow us to compare periods:
Note using SAMEPERIODLASTYEAR is similar to using DATEADD, e.g.
Total Sales Last Year 2 = CALCULATE([Total Sales], DATEADD(Dates[Date], -1, YEAR))
And if you want a measure to see the total sales this year, you can use:
Total Sales This Year = CALCULATE(SUM(Orders[Amount]), FILTER(Dates, Dates[Date].[Year] = YEAR(TODAY())))