Hello Power BI Enthusiasts,

In this blog, we’re tackling a common challenge in Power BI: calculating year-over-year (YoY) growth when some time periods are missing from your data. Whether you’re analyzing sales, revenue, or any other metric, YoY growth helps you understand performance trends over time. But what happens when your dataset has gaps—like months with no data? Standard calculations can falter, leading to inaccurate or confusing results. Don’t worry—we’ve got you covered with a practical solution, a step-by-step example, and even a tip on using AI to enhance your DAX skills.

Here’s what we’ll cover:

• The Problem: Why missing periods complicate YoY growth calculations.
• The Solution: Using SAMEPERIODLASTYEAR with logic to handle gaps.
• Practical Example: A walkthrough with a sample dataset.
• AI Prompt Engineering: How to get help from AI for this scenario.
• Practice Dataset: A downloadable file to try it yourself.

Let’s dive in!

1. The Problem: Missing Periods in YoY Growth

Year-over-year growth measures the percentage change in a metric (e.g., sales) between the current period and the same period last year. It’s a fantastic way to spot trends while factoring out seasonal variations. For example, comparing January 2024 sales to January 2023 gives you a clearer picture of growth than comparing January to December.

In Power BI, we typically use the SAMEPERIODLASTYEAR function in DAX to shift dates back one year for this comparison. A basic YoY growth measure might look like this:

This works beautifully when your data is complete. But what if some periods are missing? Imagine:

• No sales in March 2023, perhaps due to a seasonal closure or data issue, but sales exist in March 2024.
• The basic measure might try to calculate growth from zero (March 2023) to a positive value (March 2024), resulting in an infinite or undefined growth rate.
• Alternatively, missing data in the current year could skew results the other way.

These gaps can distort your analysis, mislead stakeholders, and undermine decision-making. We need a smarter approach to handle these missing periods effectively.

2. The Solution: DAX with Logic for Missing Periods

To calculate YoY growth accurately despite missing data, we can enhance our DAX measure with conditional logic. The goal is to:

• Check if data exists for the prior year’s period.
• Only calculate growth when there’s comparable data; otherwise, skip it or return a neutral value (like a blank).

We’ll use SAMEPERIODLASTYEAR combined with IF and ISBLANK to achieve this. Here’s the improved measure:

How It Works:

• CurrentPeriod: Sums the sales for the current period (e.g., March 2024).
• LastYearPeriod: Sums the sales for the same period last year (e.g., March 2023) using SAMEPERIODLASTYEAR.
• IF(ISBLANK(LastYearPeriod), BLANK(), ...):
  • If LastYearPeriod is blank (no data exists), the measure returns BLANK(), excluding that period from the growth calculation.
  • If data exists, it calculates the growth as (CurrentPeriod - LastYearPeriod) / LastYearPeriod using DIVIDE for safe division (avoiding errors if the denominator is zero).

This ensures your YoY growth is only calculated when there’s meaningful data to compare, keeping your reports accurate and reliable.

3. Practical Example: Sales Data with Incomplete Records

Let’s see this in action with a sample dataset. Suppose you have a sales table like this:

Here, March 2023 has no sales data, but March 2024 does. Using the basic YoY measure could lead to errors or misinterpretation. Let’s apply our improved measure:

• January 2024:
  • Current: 120 (Jan 2024)
  • Last Year: 100 (Jan 2023)
  • Growth: (120 - 100) / 100 = 20%
• February 2024:
  • Current: 160 (Feb 2024)
  • Last Year: 150 (Feb 2023)
  • Growth: (160 - 150) / 150 = 6.67%
• March 2024:
  • Current: 180 (Mar 2024)
  • Last Year: BLANK (Mar 2023)
  • Result: BLANK() (no growth calculated)

By returning BLANK() for March 2024, the measure avoids misleading results and focuses only on periods with valid comparisons. You can visualize this in a table or chart, and Power BI will skip the blank values, keeping your report clean.

4. AI Prompt Engineering: Getting Help with DAX

Stuck on a DAX problem? AI tools like ChatGPT can be a lifesaver. By crafting a clear prompt, you can get tailored advice to debug or improve your measures. Here’s an example:

Your Prompt: "How can I calculate year-over-year growth in DAX when some months are missing?"

Sample AI Response: "To calculate year-over-year growth in DAX when some months are missing, use the SAMEPERIODLASTYEAR function with conditional logic to handle gaps. One approach is to combine it with IF and ISBLANK to check if prior-year data exists. Here’s an example:

This checks if last year’s data is blank and skips the calculation if it is, ensuring accurate growth rates."

Why This Helps:

• Quick Validation: Confirms your approach or suggests tweaks.
• Learning Opportunity: Explains the logic, helping you master DAX.
• Flexibility: You can ask follow-ups like, “What if I want to show ‘N/A’ instead of blank?”

Try it yourself—experiment with prompts to refine your Power BI skills!

5. Practice Dataset: Try It Yourself

To solidify your understanding, we’ve prepared a sample PBIX file with the dataset above (sales data with incomplete monthly records). It includes:

• A Sales table with missing periods.
• A Date table for proper time intelligence.
• The improved YoY Growth measure ready to test.

Download it here and play around with the measure. Add visuals like tables or line charts to see how it handles missing data. Adjust the logic if needed—perhaps return “N/A” instead of BLANK()—and see the impact.

6. Conclusion: Key Takeaways

Calculating YoY growth with missing periods doesn’t have to be a headache. With the right DAX logic, you can ensure your analysis is both accurate and meaningful. Here’s what to remember:

• Missing data can skew YoY calculations—don’t let it!
• Use SAMEPERIODLASTYEAR with IF and ISBLANK to handle gaps gracefully.
• AI can be your co-pilot for tackling tricky DAX challenges.

Thanks for reading! If you found this helpful, let us know what Power BI topics you’d like us to cover next. Happy analyzing!

Best regards