One of the most common time intelligence calculations that I see in each report is that of Previous Year, this calculation is pretty easy if you know the basics of how DAX works and interacts with the filter context, but sometimes there are missing years in the dataset that can mess up your calculations.
For example, if you have 3 years in your data; 2019, 2020, and 2021, but 2020 was a year with no sales, lets say because of COVID 19 the company had to shut its operations. In this scenario you can't use functions like DATEADD or SAMPERIODLASTYEAR (which internally is transalated into DATEADD) here you have to rely on non conventional ways of calculating the Previous Year.
Data model used contains only 2 tables Dates and Sales.
And here is the sample dataset and it contains data for 2015, 2016, 2018, 2020, 2021:
First we build a simple table containing Year and Sales Amount.
Now let's use a function like DATEADD or SAMEPERIODLASTYEAR to get the previous year's Sales Amount.
Using SAMEPERIODLASTYER: You can see that in 2017 we get the Revenue of 2016 but in 2018 we do not get the revenue of 2018.
Code:
Result:
The reason why we do not se anything for 2018 is that in 2018 SAMEPERIODLASTYEAR shifts the dates in the Previous Year but there is not transaction for 2017 that's CALCULATE returns a blank because even if there is a filter context of 2017, the filter context isn't able to filter the Sales Table for Dates in 2017.
Now just to be sure that the same calculation can be achieved using DATEADD:
Code:
Result:
And here is a look at the Logical Query plan that is generated for the execution of the code containing SAMEPERIODLASTYEAR.
Solution
To achieve a correct result, we need to rely on vanilla DAX functions such as MAX.
First, we need to find the Year visible in filter context, and that can be done using MAX & SELECTEDVALUE.
Next, we need to find the immediate previous year that has a transaction in the sales table.
Now we can compute the Revenue by injecting the Previous Year variable in the filter context.
Let's see what this returns.
In the above image in 2016 we get the Revnue of 2015, and in 2017 we get the Revenue of 2016, but the problem is 2017 shouldn't exist in this report otherwise as you can see for 2018 we get the amount of 2016 so in both 2017 and 2018 we are showing the same amount, so, we can remove the year with no sales from the report by adding a bit more line of code.
Now we need to identify the Years with Sales for the years selected in the slicer (if that is applicable in your report) that's why I have used ALLSELECTED
And then in the Return part I need to write this code:
This will check if the year in the filter context is also in the YearWithSales variable and then will return the Previous Year Revenue else a blank.
This is how the report looks now:
Notice how 2017 and 2019 have been removed from the Matrix!
The complete code:
Comments