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)

YTD SamePeriodLastYear Power BI - Model

And a simple SUMX Measure to calculate Sales

Total Sales = 
SUMX(
    Sales,
    Sales[Price] * Sales[Units]
)

And I finally create this Pivot Table

YTD SamePeriodLastYear Power BI - 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.

YTD SamePeriodLastYear Power BI - PivotTable

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!

YTD SamePeriodLastYear Power BI - Modified Measure

 

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

YTD SamePeriodLastYear Power BI - Revised Year Total

 

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!

YTD SamePeriodLastYear Power BI - Final Pivot

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

 

DOWNLOAD POWER BI FILE

 

More DAX Calculations…

  1. Least Selling Product Analysis
  2. Top Selling Product Analysis 
  3. Actual v/s Budget 
  4. How to correctly using IF in Power BI
  5. COUNTIF in Power BI
  6. Change Pivot Table Field Calculations with a Slicer

 

Tagged In .


Topics that I write about...






Download Smart Ebooks on
Excel and Power BI