Power BI – Year over Year (YOY) Reports using SAMEPERIODLASTYEAR

2 Comments

In Power BI, we may want to compare periods with our data to create reports such as year over year comparisons. Power BI offers several DAX time intelligence functions. In this post we will look at SAMEPERIODLASTYEAR.

Let’s say we have an Orders table that contains orders for the last 2 years. The table contains an Order Date and a Ship Date:

We also have a standard Dates table.

Now let’s say we want to compare this year’s sales to last year’s sales.

First, create a measure for the total sales:

Formula:

We can see, the total sales amount is 2170:

Now let’s use SAMEPERIODLASTYEAR to create a new measure. This will be based on the measure we just created, Total Sales, and we will use the SAMEPERIODLASTYEAR function based on dates:

Now let’s drag across the Date from the date table, with the Total Sales and Total Sales Last Year measures. You can see we are comparing each day’s current year and previous year, for example, on February 1st, there was an amount of 160 this year and 150 last year:

Now, if we want to filter this so that it only shows results before today, we can use:

Total Sales Last Year = CALCULATE(CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date])), FILTER(Dates, Dates[Date] < TODAY()))

and

Total Sales = CALCULATE(SUM(Orders[Amount]), FILTER(Dates, Dates[Date] < TODAY()))

We can create count measures to see the comparisons as well:

Count Sales = CALCULATE(COUNT(Orders[Order Number]), FILTER(Dates, Dates[Date] < TODAY()))

And:

Count Sales Last Year = CALCULATE(CALCULATE([Count Sales], SAMEPERIODLASTYEAR(Dates[Date])), FILTER(Dates, Dates[Date] < TODAY()))

Produces below. You can see on January 1st 2018, there was 1 sale, and 2 in the previous period (January 1st, 2017):

If you want to compare additional date relationships, you can do the following. For example, we can add a new field called Total Sales by Ship Date, which uses the field Ship Date:

Total Sales by Ship Date = CALCULATE(CALCULATE (
    SUM ( Orders[Amount] ),
    USERELATIONSHIP (
        Orders[Ship Date],
        Dates[Date]
    )
)
, FILTER(Dates, Dates[Date] < TODAY()))

And add a Total Sales by Ship Date Last Year using SAMEPERIODLASTYEAR:

Total Sales by Ship Date Last Year = CALCULATE(CALCULATE([Total Sales by Ship Date], SAMEPERIODLASTYEAR(Dates[Date])), FILTER(Dates, Dates[Date] < TODAY()))

This produces:

If you would like to replace the blanks with 0, you can use the ISBLANK() DAX function.

 

ABOUT CARL DE SOUZA

Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, BI, Web, Cloud and Data Science.

carldesouza.comLinkedIn Twitter | YouTube

 

2 Responses to Power BI – Year over Year (YOY) Reports using SAMEPERIODLASTYEAR

  1. Carl this phenomenal! I cannot tell you how long that I’ve been wrapped up in trying to figure this out! Thank you, thank you, thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *