When I thought of writing this post, the most difficult task was coming up with a relevant title. Whatever I have now, is frozen, nevertheless I am sure, an avid DAX jockey would have faced this problem once in a while.
While calculating a measure for last year, it’s common practice to use SAMEPERIODLASTYEAR (or any other Time Intelligence function) that’ll help you modify the calculation for last year. The problem however happens at the Total level where (if you don’t have all values for the current year), you’d like to calculate SAMEPERIODLASTYEAR for only partial year.
I am sure I’ll do a lot better job at explaining the solution with an example. Let’s dive!
Watch a Video!
Consider this Model
We have a one to many between
- Calendar (Date Table) and
- Sales (Data from Jan 2011 till Aug 2012)
And a simple SUMX Measure to calculate Sales
Total Sales = SUMX( Sales, Sales[Price] * Sales[Units] )
And I finally create this Pivot Table
Adding another Measure to this Pivot for calculating Sales LY
Sales LY = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR ('Calendar'[Date]) )
Let’s see how our Pivot looks now.
Hmm.. 3 Problems for sure!
- I don’t want to see monthly Sales for 2011 where the monthly Sales is blank in 2012.
- At the Year Total, I’d like to see Last Year Total until August 2011 and not till December 2011. See that makes sense for comparing the same period for the Current and Last Year. And more importantly year total should update (for 9 months of last year) when I get September Sales for Year 2012.
- The Grand Total is meaning less, I don’t want it.
Modifying SAMEPERIODLASTYEAR Measure
Let’s solve for Problem 1. I want to show the Last Year Sales only if Current Year Sales is not blank. I revise the measure.
Sales LY = IF( [Total Sales] <> BLANK(), CALCULATE( [Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date]) ) )
Notice the IF that I add to restrict calculation to happen only if Total Sales is not blank. Our Pivot now looks better and shows Sales LY until Aug 2012. Perfect.. 1 down!
SAMEPERIODLASTYEAR for Partial Year
Let’s tackle the second one. Notice a couple of things
- I only want to modify the Sales LY = 18,327, for Year 2012 total. Rest numbers are correct!
- To be able to work only with the year total, I’ll need to use the function HASONEVALUE.
- I’ll then customize the total of Sales LY for the following time period
- Sales LY – Start Date – 1 Jan 2011
- Sales LY – End Date – 31 Aug 2011
I’ll further revise my Sales LY measure
Sales LY = IF( HASONEVALUE ( 'Calendar'[Month] ), IF ( [Total Sales] <> BLANK(), CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) ) ), CALCULATE ( [Total Sales], DATESBETWEEN ( 'Calendar'[Date], EDATE ( MIN ( 'Calendar'[Date] ), -12 ), EOMONTH ( MAX ( Sales[Date] ), -12 ) ) ) )
Consider the following explanation
- First off, I have an IF to check that the modification should only happen at the year total, using HASONEVALUE()
- The DATESBETWEEN is creating a dynamic range of Dates
- EDATE(MIN(‘Calendar'[Date] ), -12) Start the range from 1 Jan of last year
- EOMONTH(MAX(Sales[Date]), -12) End the range at latest sales month and modify it for last year i.e. 31 Aug 2011
Look at the revised Pivot
Removing Grand Total
Finally knocking down the last one. Since I want the calculation to only happen either for the Year or Month level, I’ll add another wrapper IF to the existing measure.
Sales LY = IF( HASONEVALUE ( 'Calendar'[Month] ), IF ( [Total Sales] <> BLANK(), CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) ) ), IF ( HASONEVALUE ( 'Calendar'[Year] ), CALCULATE ( [Total Sales], DATESBETWEEN ( 'Calendar'[Date], EDATE ( MIN ( 'Calendar'[Date] ), -12 ), EOMONTH ( MAX ( Sales[Date] ), -12 ) ) ) ) )
And my Pivot Table is now bug free!
If you are still sticking around, that must have been a long read. Let me know if you have any questions in the comments. I’d be glad to help
More DAX Calculations…
- Least Selling Product Analysis
- Top Selling Product Analysis
- Actual v/s Budget
- How to correctly using IF in Power BI
- COUNTIF in Power BI
- Change Pivot Table Field Calculations with a Slicer