Year-over-year growth in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Year-over-Year Growth Percentage

You have a sales table with columns Sales[Date] and Sales[Amount]. You want to create a measure that calculates the year-over-year growth percentage of total sales.

Which DAX measure will correctly calculate the year-over-year growth percentage?

A YoY Growth % = ([Total Sales] - CALCULATE([Total Sales], DATEADD(Sales[Date], -1, MONTH))) / CALCULATE([Total Sales], DATEADD(Sales[Date], -1, MONTH))

B YoY Growth % = ([Total Sales] - CALCULATE([Total Sales], PREVIOUSYEAR(Sales[Date]))) / CALCULATE([Total Sales], PREVIOUSYEAR(Sales[Date]))

C YoY Growth % = DIVIDE([Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Sales[Date])), CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Sales[Date])))

D YoY Growth % = ([Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Sales[Date]))) * 100

💡 Hint

Use a function that shifts the date context exactly one year back and divide the difference by last year's sales.

Option C uses SAMEPERIODLASTYEAR to get last year's sales for the same period and divides the difference by last year's sales using DIVIDE to avoid division errors.

Option C uses PREVIOUSYEAR which returns a table of the previous year but does not preserve the same period context, so it can give incorrect results.

Option C shifts by one month, not one year, so it calculates month-over-month growth, not year-over-year.

Option C multiplies the difference by 100 but does not divide by last year's sales, so it does not calculate a percentage growth.

❓ visualization

intermediate

Best Visualization for Year-over-Year Growth

You want to show year-over-year sales growth for multiple years in a Power BI report. Which visualization type is best to clearly show trends and growth percentages over time?

A Line chart showing year-over-year growth percentage by year

B Table showing sales and growth percentage side by side

C Stacked bar chart showing total sales by year

D Pie chart showing sales distribution by year

💡 Hint

Choose a visualization that clearly shows trends over time.

A line chart is best for showing trends and changes over time, such as year-over-year growth percentages.

Stacked bar charts show totals but can be harder to interpret growth percentages.

Pie charts are not good for time series data.

Tables show numbers but do not visualize trends effectively.

❓ data_modeling

advanced

Modeling Date Table for Year-over-Year Calculations

To calculate year-over-year growth correctly in Power BI, you need a proper date table. Which of the following is NOT a required characteristic of a date table for accurate time intelligence calculations?

A The date table must have a continuous range of dates without gaps

B The date table must be marked as a Date Table in Power BI

C The date table must include columns for Year, Month, and Day

D The date table must include sales amounts for each date

💡 Hint

Think about what a date table represents and what data it should contain.

A date table is a calendar table with continuous dates and date attributes like Year, Month, and Day.

It must be marked as a Date Table in Power BI to enable time intelligence functions.

However, it should not contain sales amounts or any fact data; that belongs in the fact table.

🎯 Scenario

advanced

Handling Partial Year Data in Year-over-Year Growth

Your sales data for the current year is only available up to June, but last year's data is complete. You want to calculate year-over-year growth for the available months only. Which approach will give the most accurate YoY growth?

A Calculate YoY growth using all months of last year and only available months of this year

B Filter last year's data to the same months available in the current year before calculating YoY growth

C Calculate YoY growth ignoring the months and compare total sales of both years

D Calculate YoY growth using only the last month of data available

💡 Hint

Compare like periods to get meaningful year-over-year growth.

Filtering last year's data to the same months as the current year ensures a fair comparison period.

Using all months of last year but partial months of this year inflates or deflates growth incorrectly.

Ignoring months or using only one month does not provide a reliable YoY growth.

🔧 Formula Fix

expert

Debugging Incorrect Year-over-Year Growth Measure

You wrote this DAX measure to calculate year-over-year growth percentage:

YoY Growth % = ([Total Sales] - CALCULATE([Total Sales], PREVIOUSYEAR('Date'[Date]))) / CALCULATE([Total Sales], PREVIOUSYEAR('Date'[Date]))

However, the results are incorrect and inconsistent. What is the main reason for this issue?

A PREVIOUSYEAR returns a table of the entire previous year, which may not match the current filter context, causing incorrect calculations

B The measure is missing a DIVIDE function to handle division by zero errors

C The measure should use SAMEPERIODLASTYEAR instead of PREVIOUSYEAR to shift the date context by one month

D The measure needs to multiply the result by 100 to convert to percentage

💡 Hint

Consider how PREVIOUSYEAR behaves with filter context compared to SAMEPERIODLASTYEAR.

PREVIOUSYEAR returns all dates in the previous year, ignoring the current period filter, which can cause mismatched comparisons.

SAMEPERIODLASTYEAR preserves the same period context shifted one year back, giving accurate YoY calculations.

DIVIDE and multiplying by 100 are good practices but not the root cause of incorrect results here.