Calculate SAMEPERIODLASTYEAR for Partial Year

Sales LY = 
CALCULATE(
	[Total Sales],
	SAMEPERIODLASTYEAR ('Calendar'[Date])
)

Let’s see how our Pivot looks now.

Hmm.. 3 Problems for sure!

1. I don’t want to see monthly Sales for 2011 where the monthly Sales is blank in 2012.
2. 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.
3. 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

1. I only want to modify the Sales LY = 18,327, for Year 2012 total. Rest numbers are correct!
2. To be able to work only with the year total, I’ll need to use the function HASONEVALUE.
3. 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

1. First off, I have an IF to check that the modification should only happen at the year total, using HASONEVALUE()
2. 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