Same Period Last Year to Date DAX Calculation in Power BI

I have previously written about how to calculate the same period last year calculation and compare this year’s values with the last year’s values. However, sometimes, you don’t yet have the full year, especially for the current year. You might want to compare this year’s value with the last year’s value up until the same day but last year. This is what I call same period last year to date. Here in this blog article, I’ll explain how you can do that using DAX in Power BI. To learn more about Power BI, read Power BI from Rookie to Rock Star.

Explaining the Problem

Let’s say I have a same period last year calculation like this:

Sales SPLY = CALCULATE(
    [Sales],
    SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey].[Date])
    )

This calculation works perfectly as a measure, as long as we are dealing with full periods:

The problem, however, appears when we do not have a full year like below;

In the above screenshot, we have only sales up until July 2008. The same period last year calculation at the month level is correct for that period itself (month level). However, for the whole quarter is not, because if I am comparing Qtr 3 of 2008, I just have one month of sales there (July 2008). In Qtr 3 of 2007, however, because we have sales of all months (July, August, and September 2007), the two values are not comparable. this leads to a wrong year over year calculation too.

The Solution

The correct calculation would be finding the last date that we have sales on, then find the same date but last year, and then calculate the sales of the same period last year up until that day. Like anything else in DAX, there are multiple ways of doing this, here is one method explained below.

Last date of sales

I am using the below expression to find what is the last date that we have any sales:

var lastdateAvailable=CALCULATE(MAX(FactInternetSales[OrderDate]),ALL(FactInternetSales))

Having the ALL helps me to find the last date from the sales table regardless of the filter context in the visual.

A year before that

Now that we have the last date of the sales, we can go one year back. I use the below approach;

var lastyearsameday=lastdateAvailable-365

Check for Leap Year

If there is a leap year in between this period, then the calculation above is wrong, so I added below part to check for that, and get the correct value regardless:

var ifLY=IF(DAY(lastyearsameday)<>DAY(lastdateAvailable),TRUE(),FALSE())
var lastyearsamedayLY=IF(ifLY,lastdateAvailable-366,lastyearsameday)

Same Period Last Year filtered up until that day

We have all the elements now. Using the SamePeriodLastYear combined with Filter, we can get only part of the period that is before the date we calculated in the previous step.

var SPLYUntillastdate=FILTER(
    SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey].[Date]),
    DimDate[FullDateAlternateKey].[Date]<=lastyearsamedayLY)

Full expression

Here is the full expression:

Sales SPLY to Date - Considering Leap Year = 
var lastdateAvailable=CALCULATE(MAX(FactInternetSales[OrderDate]),ALL(FactInternetSales))
var lastyearsameday=lastdateAvailable-365
var ifLY=IF(DAY(lastyearsameday)<>DAY(lastdateAvailable),TRUE(),FALSE())
var lastyearsamedayLY=IF(ifLY,lastdateAvailable-366,lastyearsameday)
var SPLYUntillastdate=FILTER(
    SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey].[Date]),
    DimDate[FullDateAlternateKey].[Date]<=lastyearsamedayLY)
return
CALCULATE(
    [Sales],
    SPLYUntillastdate)

Testing the results

Here is the result of the expression now:

As you can see, the above calculation only calculates rows up until the same day but last year. The quarter calculation and yearly calculation is also aggregating that correctly. Compared to values marked red above which is incorrectly calculating the entire period, this calculation works perfectly fine for comparison of this year vs last year.

Note that, like anything else in DAX, this calculation can be done in many other ways too. this method is only one of the many possible options.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.