Microsoft Certified Power Bi Data Analyst Associate · Free Practice Question Hard
Question 8
CASE ANALYSIS:
BrightMart Retail, a rapidly expanding chain of retail stores, needs an advanced Power BI solution to gain deep insights into their sales performance, customer behavior, and inventory trends. The company operates in multiple regions and has diverse customer segments.
They require a secure, optimized, and scalable dashboard that allows:
Advanced segmentation of customers based on buying behavior.
Dynamic ranking of stores based on sales performance.
Row-Level Security (RLS) to ensure users only see relevant data.
Real-time KPI monitoring using advanced DAX calculations.
Integration with external APIs for competitor pricing analysis.
Data Set Overview
Business Question:
To analyze store performance, BrightMart wants to calculate the "Year-to-Date Sales Growth %" dynamically using DAX. Which formula correctly computes this metric?
-
A
- YTD Growth % =
- CALCULATE([Total Sales], DATESYTD('Date'[Date])) - CALCULATE([Total Sales],
- PREVIOUSYEAR('Date'[Date]))
-
B
- YTD Growth % =
- DIVIDE(
- [Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])),
- CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date])))
-
C
- YTD Growth % =
- SUM([Total Sales]) - SUM(PREVIOUSYEAR([Total Sales]))
-
D
- YTD Growth % =
- VAR CurrentSales = [Total Sales]
- VAR LastYearSales = LOOKUPVALUE([Total Sales], 'Date'[Year], YEAR(TODAY()) - 1)
- RETURN DIVIDE(CurrentSales - LastYearSales, LastYearSales)
Reveal correct answer
Correct answer: B
Explanation
Create DAX Formula Step-by-Step
Define Total Sales Measure
To calculate the Year-to-Date (YTD) Sales Growth % dynamically for BrightMart Retail, first create a base measure: TotalSales = SUM(Sales[TotalAmount]). Ensure a Date table exists (e.g., Date = CALENDAR(MIN(Sales[Date]), MAX(Sales[Date]))) and is marked as the date table (Modeling > Date Table > Mark as Date Table), linked to Sales[Date].
Calculate YTD Growth %
Use the formula: YTDGrowth% = DIVIDE([TotalSales] - CALCULATE([TotalSales], SAMEPERIODLASTYEAR('Date'[Date])), CALCULATE([TotalSales], SAMEPERIODLASTYEAR('Date'[Date]))). In Power BI Desktop, go to Modeling > New Measure and enter this. It calculates current YTD sales ([TotalSales] with implicit DATESYTD from filters), subtracts last year’s YTD sales (via SAMEPERIODLASTYEAR), and divides by last year’s value for a percentage. DIVIDE handles division-by-zero gracefully. The Microsoft DAX SAMEPERIODLASTYEAR function reference states it "shifts the date context to the prior year," while DIVIDE "safely computes ratios," ensuring dynamic accuracy.
Why Not Other Approaches?
Simple Difference
YTDGrowth% = CALCULATE([TotalSales], DATESYTD('Date'[Date])) - CALCULATE([TotalSales], PREVIOUSYEAR('Date'[Date])) gives an absolute difference, not a percentage, missing the growth rate requirement. The DATESYTD documentation focuses on "time periods."
SUM Difference
YTDGrowth% = SUM([TotalSales]) - SUM(PREVIOUSYEAR([TotalSales])) is invalid—PREVIOUSYEAR works with dates, not columns, and it’s not a percentage. The SUM documentation lacks time intelligence.
LOOKUPVALUE Approach
YTDGrowth% = VAR CurrentSales = [TotalSales] VAR LastYearSales = LOOKUPVALUE([TotalSales], 'Date'[Year], YEAR(TODAY())-1) RETURN DIVIDE(CurrentSales - LastYearSales, LastYearSales) fails—LOOKUPVALUE retrieves single values, not YTD aggregates, and isn’t dynamic. The LOOKUPVALUE documentation limits its use.
Additional Considerations
Date Table: Ensure a continuous date range and relationship to Sales[Date], per the date table guidance.
Format: Set the measure format to Percentage in the Modeling tab.
Final Answer
Use YTDGrowth% = DIVIDE([TotalSales] - CALCULATE([TotalSales], SAMEPERIODLASTYEAR('Date'[Date])), CALCULATE([TotalSales], SAMEPERIODLASTYEAR('Date'[Date]))) to dynamically calculate Year-to-Date Sales Growth % for BrightMart Retail, accurately reflecting store performance over time.
A.
DATESYTD() does not correctly compute YTD growth percentage.
B.
This formula correctly calculates YTD sales growth dynamically using SAMEPERIODLASTYEAR() to compare current and previous years.
C.
PREVIOUSYEAR([Total Sales]) is not a valid syntax.
D.
LOOKUPVALUE does not work efficiently with time-based calculations.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
