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 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:
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)
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.
6 thoughts on “Same Period Last Year to Date DAX Calculation in Power BI”
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.
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.
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
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?
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.
I’m still fairly new to PowerBI and feel like I’m missing an important concept to why this isn’t working.
to help you with your DAX question, I need to have your PBIX file