Same Period Last Year to Date DAX Calculation in Power BI

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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:

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:

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;

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:

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.

Full expression

Here is the full expression:

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.

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

6 thoughts on “Same Period Last Year to Date DAX Calculation in Power BI

  • Hi Reza:
    Your resuklts are correct, but the user may see strange results.
    Let’s say that today is Dec 05, 2019.
    In your Chart, the value for Dec 2018 will show sales up to Dec 31.
    The calculated value will show results for December 01 to 05, 2018, which will probably differ.
    —————————-
    Other consideration regarding the way you calculate “same day last year”:
    For 28/Feb/2017 it will get 28/Feb/2016, so sales on 29/Feb/2016 will not be included!
    —————————-
    Calendars are always tricky and confusing, unless a clear explanation is given to the user as how you are considering your calculations.
    —————————–

    • Hi Roberto
      The first scenario you mentioned is covered already. if the last date is 5th of Dec, it would compare it with 5th of Dec of the last year.
      However, I agree about the leap year scenario. there is no solid answer. that should be designed based on the requirement. Does the user want to do the analysis considering 365 days? or if there is a leap year, consider the 29th too? based on each of those the calculation can change.
      This is a sample showing how the calculation should be done. My articles are never written in a way that “copy this code and you will be fine!”. modifications and adjustments are always needed.
      Cheers
      Reza

  • Hello Reza:

    I work in a retail company and we need to compare same period last year to the same day of last year and not the same date, for example, the sales of yesterday, Monday February 17th 2020 should be compared to the sales of Monday February 18th 2019, applying same period last year if gives incorrect answer because it compares the sales of Monday February 17th 2020 to Sunday February 17th and should add 1 day, and it gets more difficult becase i need to calculate with leap years as well, ¿Any ideas on the approach i should take?, especially handling the leap year with the day to day last year comparison

    • Hi Enrique
      How do you check and see that the last year the same DAY of 17th Feb 2020 is 18th Feb 2019? do you first check the week number? Iso week or normal week? and then do you get the same day of the week for the last year?
      Cheers
      Reza

  • Hi Reza,
    Thank you very much for this solution which I have used on a sales dataset, however another project requires use of a dataset that has a line in my fact table for each component of the BOM. So in order to get top level summarized data for each order I have been using SUMX(FILTER(Sales,BOMLevel=#),SalesLineValue).

    I tried 2 things, both of which didn’t work.
    1.
    return
    CALCULATE([SalesLineValue],BOMID=0,SPLYUntilllastdate)
    2.
    return
    CALCULATE(SUMX(FILTER(‘Sales’,BOMID=0),SalesLineValue),SPLYUntilllastdate)

    I’m still fairly new to PowerBI and feel like I’m missing an important concept to why this isn’t working.

Leave a Reply

%d bloggers like this: