Advanced DAX and Time Intelligence Interview Questions for Power BI
25 Advanced DAX and Time Intelligence questions with answers for power bi roles — 3 easy, 11 medium, 11 hard.
About Advanced DAX and Time Intelligence
Advanced DAX separates mid and senior candidates. Cover YTD, previous year, YoY growth, rolling averages, running totals, fiscal year handling, calculation groups, field parameters, visual calculations, TREATAS, RANKX, ISINSCOPE, KEEPFILTERS, semi-additive balances, new customer counts and top N with Others, with tested DAX patterns you can reuse.
Sheet progressStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
Time intelligence functions compare values across periods using a proper date table, for example year-to-date, same period last year, moving averages and quarter-over-quarter growth. Key functions are TOTALYTD, DATESYTD, SAMEPERIODLASTYEAR, DATEADD, PARALLELPERIOD and DATESINPERIOD. They return sets of dates used as CALCULATE filters, so the date table must be contiguous, marked as a date table and related to the facts.
Sales YTD = TOTALYTD([Total Sales], 'Date'[Date], "31/3")
Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
YoY % =
VAR cur = [Total Sales]
VAR py = [Sales PY]
RETURN DIVIDE(cur - py, py)
The "31/3" argument makes YTD follow an April to March fiscal year.
03
Write a measure that shows the selected slicer value in a title.
Report Title =
VAR r = SELECTEDVALUE('Store'[Region], "All Regions")
VAR y = SELECTEDVALUE('Date'[Fiscal Year], "All Years")
RETURN "Sales for " & r & " - " & y
Use it in a visual title through conditional formatting, Title text, Field value. SELECTEDVALUE returns the fallback text when zero or multiple values are selected.
04
What is the difference between SAMEPERIODLASTYEAR, DATEADD and PARALLELPERIOD?
SAMEPERIODLASTYEAR shifts the current dates back exactly one year. DATEADD shifts by any number of days, months, quarters or years, such as DATEADD('Date'[Date], -1, MONTH) for last month. PARALLELPERIOD also shifts by an interval but returns the full shifted period, for example the whole previous quarter even if only one month is selected. Choose based on whether partial periods should be compared.
05
How do you handle a fiscal year in time intelligence functions?
Functions like TOTALYTD and DATESYTD accept a year-end date argument, so for an Indian fiscal year ending 31 March use TOTALYTD([Sales], 'Date'[Date], "31/3"). Add fiscal year, quarter and month columns to the date table for slicing. For complex calendars such as 4-4-5 retail weeks, standard functions do not work, and you write custom logic using fiscal period columns. Newer calendar-based time intelligence also supports custom calendars.
Field parameters let report users switch which measure or dimension a visual shows using a slicer, for example choosing between Sales, Profit and Units, or switching the axis between Region and Category. They are created through Modeling, New parameter, Fields, which generates a calculated table. They reduce the number of duplicate visuals and bookmarks needed for dynamic reports.
Key table functions include FILTER, ALL, VALUES, DISTINCT, SUMMARIZE and SUMMARIZECOLUMNS for grouping, ADDCOLUMNS to add calculated columns, TOPN for top rows, CROSSJOIN, UNION, EXCEPT, INTERSECT, GENERATESERIES and TREATAS for virtual relationships. They return tables used inside iterators, CALCULATE filters or calculated tables. ADDCOLUMNS over SUMMARIZE is a common pattern for reliable grouped results.
08
Explain how RANKX works and common mistakes with it.
RANKX iterates a table, evaluates an expression for each row and ranks the current value among them. A common pattern is RANKX(ALL('Product'[Product]), [Total Sales]). Mistakes include not using ALL, so every product ranks 1 because only one is visible, ranking over a table with duplicates, and forgetting ties handling with the Dense option. The newer RANK function supports multi-column ordering.
Visual calculations are DAX calculations defined directly on a visual and computed on the visual's own aggregated data, rather than on the model. They make running totals, moving averages, percent of parent and comparison with previous rows simpler using functions like RUNNINGSUM, MOVINGAVERAGE and PREVIOUS. They do not add measures to the model, so they suit visual-specific logic but cannot be reused across visuals.
10
What is a disconnected table and how is it used for what-if analysis?
A disconnected table has no relationship to the model and is used to capture a user's choice through a slicer. For example, a Discount table from 0 to 30 percent created with GENERATESERIES, or the What-if parameter feature, lets a measure read SELECTEDVALUE(Discount[Value]) and calculate projected revenue. Since it does not filter facts, the measure decides how the selection is applied.
11
Business wants year-over-year growth, but the current year is incomplete. How do you compare fairly?
Comparing a partial year with a full last year understates growth. I would limit last year to the same date range, for example using the last date with sales this year and filtering last year's dates up to the matching day, or compare year-to-date to previous year-to-date. I would label the visual clearly as YTD versus same period last year so readers understand it.
12
A YTD measure returns blank for future months on a chart. Users want the line to stop at today. How?
The issue is the opposite for many teams: YTD repeats the last value for future months. I would add a condition so the measure returns BLANK when the period starts after the last date with data, for example IF(MIN('Date'[Date]) > [Last Sales Date], BLANK(), TOTALYTD(...)). Alternatively add a column flagging dates up to today in the date table and filter the visual on it.
Running Total =
VAR maxDate = MAX('Date'[Date])
RETURN
CALCULATE(
[Total Sales],
'Date'[Date] <= maxDate,
REMOVEFILTERS('Date')
)
REMOVEFILTERS clears the current period filter so all earlier dates are included. In a visual-only scenario, the visual calculation RUNNINGSUM is simpler.
Product Rank =
IF(
ISINSCOPE('Product'[Product]),
RANKX(ALLSELECTED('Product'[Product]), [Total Sales], , DESC, DENSE)
)
ALLSELECTED ranks within the products visible after slicers, and ISINSCOPE hides the rank on total rows.
Calculation groups are reusable sets of calculation items that modify any measure, such as Current, YTD, Previous Year and YoY %, using SELECTEDMEASURE(). Instead of writing YTD versions of 30 measures, you write one item that applies to all. They can be created in Power BI Desktop model view or Tabular Editor. They reduce measure sprawl but change how measures behave, so naming and format strings need care.
TREATAS applies the values of one table or column as filters on columns of another table, creating a virtual relationship without a physical one. For example, CALCULATE([Budget], TREATAS(VALUES('Product'[Category]), Budget[Category])) filters budget by selected categories even when no relationship exists. It is useful for different-grain tables or disconnected slicers, though physical relationships usually perform better.
17
What is the difference between ISFILTERED, ISINSCOPE and HASONEVALUE?
ISFILTERED returns true if a column has a direct filter. HASONEVALUE returns true when exactly one value is visible, useful before SELECTEDVALUE. ISINSCOPE returns true when a column is the current grouping level in a visual hierarchy, which is the most reliable way to detect subtotal versus detail rows in matrices. For example, IF(ISINSCOPE('Product'[Product]), [Sales], BLANK()) hides subtotals.
KEEPFILTERS changes how a CALCULATE filter argument combines with the existing filter context: instead of replacing filters on that column, it intersects with them. For example, CALCULATE([Sales], KEEPFILTERS('Product'[Color] = "Red")) shows blank for Blue rows in a colour table, while without KEEPFILTERS every row would show Red sales. It is useful for measures that should respect user selections.
19
Thirty measures each need YTD, previous year and YoY versions. How do you avoid 120 measures?
I would create a calculation group, such as Time Calculation, with items Current, YTD, PY and YoY % that use SELECTEDMEASURE() wrapped in the time intelligence logic, and set format strings for percent items. Users pick the item from a slicer or matrix columns. This keeps the model small and consistent. I would document it because calculation groups change measure behaviour everywhere.
20
Managers want to see the top 5 products plus an 'Others' row that sums the rest. How do you build it?
I would create a calculated table of product names plus an Others row, related or disconnected, and a measure that ranks products with RANKX over ALL products. For rows ranked 1 to 5 it returns their sales; for Others it returns total sales minus the top 5. TOPN helps compute the top 5 total. The rank should respect slicers like year so the top 5 updates dynamically.
21
A measure works in a card but gives wrong results in a matrix with subtotals. How do you debug it?
I would test the measure at each level of the matrix using a table visual and check how filter context differs at the subtotal. Often logic uses SELECTEDVALUE or MAX, which return blank or misleading results when several values are visible. I would use ISINSCOPE to branch logic or SUMX over the grouping column for additive results, and validate with DAX Studio queries.
Sales 3M Avg =
VAR periods =
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH)
RETURN
CALCULATE(
AVERAGEX(VALUES('Date'[Month Year]), [Total Sales]),
periods
)
AVERAGEX over months averages monthly totals instead of daily values. Month Year must be a column in the date table.
23
Write a measure that counts new customers in a period.
New Customers =
VAR minDate = MIN('Date'[Date])
RETURN
COUNTROWS(
FILTER(
VALUES(Sales[CustomerID]),
CALCULATE(MIN(Sales[OrderDate]), REMOVEFILTERS('Date')) >= minDate
)
)
A customer is new if their first ever order falls in the current period.
24
Tell me about the most complex DAX problem you solved.
Situation: finance needed month-end inventory balances, which are semi-additive and must not be summed across months. Task: build balances that work at any date level. Action: I wrote a measure using LASTNONBLANK over dates to take the closing balance per period and SUMX across warehouses, then validated against ERP reports for six months. Result: the report replaced a manual Excel process and matched ERP within rounding.
25
Write a measure to calculate a closing balance that is not summed across months.
Closing Balance =
CALCULATE(
SUM(Inventory[Qty]),
LASTNONBLANK('Date'[Date], CALCULATE(COUNTROWS(Inventory)))
)
Balances are semi-additive: they sum across warehouses but not across time. This takes the last date with data in each period. LASTDATE works when balances exist for every day.
Frequently asked questions
How important is Advanced DAX and Time Intelligence in Power BI interviews?
Advanced DAX separates mid and senior candidates. Cover YTD, previous year, YoY growth, rolling averages, running totals, fiscal year handling, calculation groups, field parameters, visual calculations, TREATAS, RANKX, ISINSCOPE, KEEPFILTERS, semi-additive balances, new customer counts and top N with Others, with tested DAX patterns you can reuse.
How should I use the Advanced DAX and Time Intelligence sheet?
Go top to bottom — easy to hard. Answer before opening each answer, tick what you solved and save misses to Last-Minute Revision.