DAX Scenario Based Interview Questions

Here, we will discuss DAX Scenario Based Interview Questions, which interviewers ask in most company interviews, mainly for Business Analyst job positions.

1. What is DAX?

DAX (Data Analysis Expressions) is a formula language used in Power BI, Excel, and SQL Server Analysis Services for advanced analytics. It enables calculations, aggregations, and data manipulation in tabular models.

2. DAX Interview Topics

Contexts: Row vs. Filter context (e.g., CALCULATE modifies filter context).
Functions: Core (SUM, AVERAGE), Time Intelligence (TOTALYTD, SAMEPERIODLASTYEAR), Logical (IF).
Measures vs. Columns: Runtime calculations vs. static storage.
Relationships: Use of RELATED/RELATEDTABLE across tables.
Iterators: Aggregators (SUMX, AVERAGEX) for row-wise operations.
Variables: Simplify complex logic with VAR.
Optimization: Avoid circular dependencies, and minimize row context overhead.
Error Handling: Use IFERROR or ISBLANK.
Time Intelligence: Year-over-year (YoY) growth, moving averages.
Evaluation Order: How DAX processes filters and context transitions.

1. How would you create a rolling 3-month average of sales, excluding the current month?

Rolling 3-Month Average (Excluding Current Month)

Uses DATESINPERIOD to define a dynamic 3-month window ending on the last day of the previous month (EOMONTH(TODAY(), -1)).
Averages sales over this closed period for stable, month-end-aligned insights.

Rolling 3-Month Avg Sales = 
CALCULATE(
    AVERAGE(Sales[SalesAmount]),
    DATESINPERIOD(
        Sales[Date],
        EOMONTH(TODAY(), -1), // End of last month
        -3, // Look back 3 months
        MONTH
    )
)

2. Write a measure to calculate the percentage of total sales for each product category, but only for the top 5 selling products overall.

Top 5 Product Contribution by Category

Identifies top 5 products via TOPN and SUMMARIZE, ranking by total sales.
Calculates their combined sales (TotalTopSales) and divides individual product sales by this total.
Note: Requires proper relationships between Sales and Products tables.

Top 5 Product Sales % = 
VAR TopProducts = 
    TOPN(
        5,
        SUMMARIZE(Sales, Products[ProductID], "TotalSales", SUM(Sales[SalesAmount])),
        [TotalSales],
        DESC
    )
VAR TotalTopSales = SUMX(TopProducts, [TotalSales])
RETURN
DIVIDE(SUM(Sales[SalesAmount]), TotalTopSales, 0)

3. Create a DAX expression to find the date of the last sale for each customer, but only if they’ve purchased in the last 6 months.

Last Purchase Date (Active Customers Only)

Uses FILTER and EARLIER (for row context) to isolate sales within the last 6 months (EDATE(TODAY(), -6)).
Returns MAX(SaleDate) only if the customer meets the recency criteria.

Last Sale Date = 
CALCULATE(
    MAX(Sales[SaleDate]),
    FILTER(
        Sales,
        Sales[CustomerID] = EARLIER(Sales[CustomerID]) &&
        Sales[SaleDate] >= EDATE(TODAY(), -6) // Within the last 6 months
    )
)

4. Develop a measure that calculates the year-over-year growth rate for each product, handling scenarios where there were no sales in the previous year.

Year-over-Year Growth (Handling Zero Sales)

Compares current-year sales to prior-year values using SAMEPERIODLASTYEAR.
Returns BLANK() if prior-year sales are absent, preventing division errors. Replace with 0 if zero-based reporting is needed.

YoY Growth Rate = 
VAR CurrentYearSales = SUM(Sales[SalesAmount])
VAR LastYearSales = 
    CALCULATE(
        SUM(Sales[SalesAmount]),
        SAMEPERIODLASTYEAR(Sales[Date])
    )
RETURN
IF(
    ISBLANK(LastYearSales),
    BLANK(), // or 0, depending on how you want to handle it
    DIVIDE(CurrentYearSales - LastYearSales, LastYearSales, 0) * 100
)

5. Calculate the median transaction value per customer, excluding their highest and lowest purchases.

Median Transaction Value

Creates a transaction list per customer, excluding min/max values via FILTER and MINX/MAXX.
Uses MEDIANX on the cleaned dataset.
Edge Case Tip: Returns blank if a customer has ≤2 transactions.

Median Transaction Value = 
VAR CustomerTransactions = 
    FILTER(
        Sales,
        Sales[CustomerID] = EARLIER(Sales[CustomerID])
    )
VAR ExcludedTransactions = 
    FILTER(
        CustomerTransactions,
        Sales[SalesAmount] <> MAXX(CustomerTransactions, Sales[SalesAmount]) &&
        Sales[SalesAmount] <> MINX(CustomerTransactions, Sales[SalesAmount])
    )
RETURN
MEDIANX(ExcludedTransactions, Sales[SalesAmount])

Table of Contents