Articles
Power BI Time Intelligence Interview Questions: Fiscal Calendars and Partial Periods
Practice Power BI time intelligence interviews with fiscal-year boundaries, matched partial periods, completion cutoffs, and YTD totals without double counting.
A dashboard says revenue is down 60%, but finance says the comparable period is up 20%. Both numbers can come from correct arithmetic applied to different windows. Before debugging the DAX, establish what “this year,” “last year,” and “through today” mean in the company's calendar and data pipeline.
For Power BI time intelligence interview questions, the strongest answer connects a calendar definition, a date relationship, and a comparison cutoff. A function name does not settle fiscal-year naming, retail weeks, missing dates, or whether an unfinished month should be compared with an entire completed month.
Evidence boundary: Microsoft documentation supplies the function and model behavior cited here. The revenue fixtures and interview cases are original examples, checked with Python arithmetic rather than a Power BI engine. No captured PBIX test or employer interview format is claimed. Current documentation still labels calendar-based time intelligence preview; confirm the target environment before using it.
Which calendar does the business use?
Ask the interviewer to define the fiscal boundary before writing a year-to-date measure. A fiscal year starting July 1 can still use ordinary Gregorian months. A 4-4-5 retail calendar divides a quarter into periods of four, four, and five weeks. These are different contracts, even if both are called fiscal calendars.
In an original July-start example, finance names the year by its ending year. July 1, 2025 through June 30, 2026 is FY2026. April 10, 2026 belongs to fiscal month 10 and fiscal quarter 4. Another organization might use a starting-year label; the date range does not change, but the label does.
| Business date | Fiscal year label in this example | Fiscal month | Fiscal quarter |
|---|---|---|---|
| July 1, 2025 | FY2026 | 1 | 1 |
| April 10, 2026 | FY2026 | 10 | 4 |
| June 30, 2026 | FY2026 | 12 | 4 |
| July 1, 2026 | FY2027 | 1 | 1 |
For retail periods, obtain the approved boundary table and the treatment of an extra week. Do not invent a universal 53-week rule. “Same week last year” could mean a fiscal-week mapping, a weekday-aligned comparison, or a calendar-date shift. State which one the measure implements.
A useful follow-up is whether the report contains multiple calendars. Sales may use the corporate fiscal calendar while a regulatory report uses calendar years. Make the selected calendar visible in measure names and report labels rather than making users infer it from a number.
What must the date model guarantee?
Official classic-model guidance: Microsoft's date-table guidance requires a date column with unique, nonblank values, no missing dates, and full-year coverage. The year need not be January–December. Classic time intelligence requires marking the date table; calendar-based use has different requirements and exceptions. Microsoft date-table guidance.
The date dimension and fact table have different jobs. A complete date dimension can contain July 9 even if there were no sales that day. Removing that date because the fact table has no row confuses a calendar with observed activity.
Check the relationship's key and intended date role. An order timestamp containing hours is not automatically equal to a date-only midnight key. Decide the reporting time zone before deriving a business date; a late-night UTC event may belong to a different local business day.
If Sales contains OrderDate and ShipDate, ask which role the measure uses. A fiscal YTD shipping metric needs a shipping-date filtering path. Renaming a measure “Shipped Revenue YTD” does not change an active relationship to OrderDate.
Inspect the visual's date fields too. A month label taken from the fact table, an unrelated calendar, or a hidden hierarchy can produce a different filter path from the intended dimension. Trace which table supplies the slicer before assuming the function is broken.
How do classic and calendar-based formulas differ?
Official distinction: Microsoft's current time-intelligence guide separates classic date-column functions from calendar-based functions using calendar metadata. The preview supports week-based and non-Gregorian calendar patterns; classic behavior fits Gregorian or shifted Gregorian calendars. Microsoft time-intelligence guide.
For a classic July-start fiscal year, an illustrative measure is:
Sales FYTD =
CALCULATE(
[Sales],
DATESYTD('Date'[Date], "6-30")
)
Official syntax boundary: DATESYTD permits the year-end string only with date-column syntax, and its format depends on the client's locale. The example above assumes a locale accepting that month-day form. A calendar reference supplies its own definition; do not add the classic year-end argument to it. Microsoft DATESYTD reference.
For a correctly defined calendar named FiscalCalendar, the corresponding calendar-input shape is:
Sales FYTD Calendar =
CALCULATE([Sales], DATESYTD(FiscalCalendar))
Merely naming a table FiscalCalendar does not document its tagged calendar metadata. Establish which input the model actually provides and which feature set the interview environment supports.
The classic year-end argument does not encode a 4-4-5 boundary table. A June 30 cutoff and a retail year ending on an approved Saturday can disagree. If the required calendar is unsupported in the environment, explain an explicit date-range or mapping design instead of presenting the classic formula as a universal solution.
Microsoft's preview also allows sparse-calendar behavior beyond classic requirements. That does not make arbitrary missing data a legitimate calendar design. Identify intentional excluded dates and verify that the resulting “previous day” matches the business meaning. Keep this distinction separate from missing sales facts.
How do partial periods reverse the growth story?
Use this original fixture. Data for the current period is certified complete through July 10, 2026. The company wants July-to-date compared with the same dates in 2025.
| Comparison window | Revenue |
|---|---|
| July 1–10, 2026 | 120 |
| July 1–10, 2025 | 100 |
| July 11–31, 2025 | 200 |
| Full July 2025 | 300 |
The matched growth is (120 - 100) / 100 = 20%. Comparing the same current 120 with the prior full-month 300 gives (120 - 300) / 300 = -60%. The calculation is arithmetically valid but answers another question.
The saved Python check confirms both outcomes. It does not prove any DAX expression produces these windows. To validate the actual measure, inspect or export the dates selected by each side and reconcile the result against the fixture.
Distinguish month-to-date from fiscal-year-to-date. At the start of this July-start fiscal year their windows coincide, making an incorrect measure appear correct. Repeat the check in August or April so the test exposes the difference.
Which date is a trustworthy cutoff?
“Through today” can mean the user's current date, the latest transaction date, or the last date the pipeline certifies complete. These are not interchangeable.
In an original scenario, Store A last sold something on July 8, but ingestion is complete through July 10. Zero activity on July 9–10 does not justify shortening A's comparison window. Conversely, one July 10 transaction arriving early does not prove all July 10 transactions have arrived.
Use an explicit completion watermark when the metric requires complete data. Agree whether it is global, per source, or per business unit. A global cutoff improves comparability but may withhold fresher data from one unit; separate cutoffs improve recency but complicate comparisons. These are reporting decisions, not defaults supplied by YTD functions.
The maximum date in a calendar extending into next year is also not a completion watermark. In a future-period visual, decide whether the measure should return blank or carry the latest available YTD. Label that policy and test it.
A practical implementation plan is to determine the requested end date, cap it by the approved loaded-through date, derive the fiscal start, and evaluate Sales over that explicit range. For the prior side, use the approved comparison mapping and matching cutoff. Preserve product and region filters while replacing only the intended date context. Do not remove every filter just to make a number appear.
Does shifting dates guarantee an equivalent comparison?
Official function boundary: classic DATEADD date-column input requires a contiguous selected interval. Microsoft also documents end-of-month extension behavior; calendar input exposes additional extension and truncation controls. Microsoft DATEADD reference.
An arbitrary selection of two separated weeks can therefore need a different contract from one continuous month. Ask whether the desired comparison shifts each selected date, shifts an enclosing period, or compares a defined fiscal period. Those choices can legitimately yield different totals.
For February 29, define the intended prior-year comparison before selecting an implementation. Matching day-of-year positions, matching month-day dates, and matching retail weekdays are distinct policies. Do not promise that subtracting 365 days or shifting one year always implements all of them.
For a 4-4-5 calendar, an approved fiscal mapping can identify the prior comparable period and day position. Test the final period of a 53-week year explicitly. A metric that compares 35 current days with 28 prior days may need normalization or a different matched window; get the business decision before coding that adjustment.
Avoid comparing only the displayed year labels. FY2027 period 1 and FY2026 period 1 are different periods. Use a year-qualified key for identifying a specific period, while retaining ordinal period numbers for appropriate comparisons and sorting.
Why is the YTD grand total different from the row sum?
Consider a second original fixture with monthly Sales of July 120, August 80, and September 100.
| Row | Monthly Sales | Fiscal YTD |
|---|---|---|
| July | 120 | 120 |
| August | 80 | 200 |
| September | 100 | 300 |
Adding the YTD rows gives 620, counting July repeatedly. Fiscal YTD through September is 300. The saved arithmetic check confirms the intended result independently of a visual.
Explain that the total must be evaluated against its intended date context. If the product asks for the sum of displayed row calculations, that is a separate measure definition; do not silently substitute it for a cumulative total.
Growth percentages have a similar problem. Averaging regional growth rates usually answers a different question from computing growth on combined current and prior revenues. Name the aggregation rule and test a region with a small baseline next to a large region.
Also define a zero or missing prior baseline. Should growth be blank, display “new,” or follow another approved policy? A divide-by-zero workaround does not decide the business meaning. Preserve the distinction between no data and genuine zero revenue when that distinction matters.
How would you verify the measure in an interview?
Use boundary cases that can falsify your definition: June 30 and July 1, a partial month, an August selection, a zero-activity day, a future month, an incomplete source, a leap-day comparison, and a retail extra week. Reconcile the selected dates and values, not just a formatted percentage.
Then test product and region slicers plus the intended date role. A fiscal calculation that loses a product filter can look plausible while reporting company-wide revenue. Keep a small independent expected-result table so a matching visual total is not your only oracle.
| Practice question | Transfer to time intelligence |
|---|---|
| Compute a Rolling Seven-Day Revenue Sum with Missing Dates | Distinguish calendar gaps from no-activity dates. |
| Count weekly customers with ≥$1000 YTD spend | Define cumulative windows before weekly aggregation. |
| Build cohort 30-day retention from signup date | Account for incomplete observation windows. |
| Define and Implement Natural-Month Date Comparison | Make month and leap-year rules explicit. |
| Reconcile Monthly Bank-Account Balances Across Two Datasets | Align period keys and reconcile expected values. |
A concise interview explanation should name the fiscal calendar, the fact-date relationship, the completion cutoff, and the prior-period mapping before it names a DAX function. That sequence makes your formula reviewable.