Question

I need to compute a year-over-year (YoY) growth rate but only for months where I have data in both years—how do I do this?
I want to calculate the year-over-year (YoY) growth rate in Power BI, but I only want to include months where data is available for both the current and previous year. What is the best approach using DAX functions like CALCULATE, SAMEPERIODLASTYEAR, or INTERSECT to ensure accurate comparisons?

Answer 1

Calculating Year-over-Year (YoY) growth only for those months for which there are entries in both years, you would use DAX like this:

DAX Measure for YoY Growth with Matching Months

YoY Growth % = 
VAR CurrentYearSales = SUM( 'Sales'[TotalSales] )
VAR PreviousYearSales = 
    CALCULATE( 
        SUM( 'Sales'[TotalSales] ), 
        SAMEPERIODLASTYEAR( 'Date'[Date] ) 
    )

VAR MonthsWithData = 
    INTERSECT( 
        VALUES( 'Date'[MonthYear] ), 
        CALCULATETABLE( VALUES( 'Date'[MonthYear] ), SAMEPERIODLASTYEAR( 'Date'[Date] ) ) 
    )

VAR AdjustedCurrentSales = 
    CALCULATE( CurrentYearSales, 'Date'[MonthYear] IN MonthsWithData )

VAR AdjustedPreviousSales = 
    CALCULATE( PreviousYearSales, 'Date'[MonthYear] IN MonthsWithData )

RETURN 
    IF( AdjustedPreviousSales > 0, 
        DIVIDE( AdjustedCurrentSales - AdjustedPreviousSales, AdjustedPreviousSales ), 
        BLANK()
    )

The rationale for the process is:

Compute the Sales for both years.

Current Year Sales = Total sales for the current period.

Previous Year Sales = Sales for the same period in the last year's calculations using the function SAME-PERIOD-LAST-YEAR( ).

Find Common Months with Data

The function INTERSECT( ) extracts only those months that exist in both the current and previous years.

Filter Sales for Matching Months

Adjusted Current Sales and Adjusted Previous Sales ensure that only months with data in both years are considered.

Calculate YoY growth

The DIVIDE( ) function is for safe division, which means it can return BLANK if a valid comparison cannot be performed.