A quick guide to Time Intelligence DAX in Power BI with real-world interview questions, clear explanations, and practical use cases.
1. YTD Sales Calculation
Question: You have a sales table. How will you calculate Year-to-Date (YTD) sales?
Answer:
Sales YTD = TOTALYTD( SUM(Sales[Amount]), 'Date'[Date] )
Scenario Explanation:
Used when business wants to track performance from the start of the year till today.
2. Previous Year Sales
Question: How will you compare current year sales with previous year?
Answer:
Sales PY = CALCULATE( SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]) )
Scenario: Used in YoY comparison dashboards.
3. Year-over-Year Growth %
Question: How do you calculate YoY growth?
Answer:
YoY Growth % =
DIVIDE(
[Sales] - [Sales PY],
[Sales PY]
)
Scenario: Business wants growth percentage between years.
4. Month-over-Month Growth
Question: Calculate MoM growth.
Answer:
Sales PM = CALCULATE( [Sales], PREVIOUSMONTH('Date'[Date]) ) MoM Growth % = DIVIDE( [Sales] - [Sales PM], [Sales PM] )
5. Rolling 12 Months Sales
Question: How to calculate last 12 months rolling sales?
Answer:
Rolling 12M Sales = CALCULATE( [Sales], DATESINPERIOD( 'Date'[Date], MAX('Date'[Date]), -12, MONTH ) )
Scenario: Used for trend analysis ignoring yearly boundaries.
6. Same Period Last Year vs Current
Question: Difference between SAMEPERIODLASTYEAR and DATEADD?
Answer:
Sales LY (DATEADD) = CALCULATE( [Sales], DATEADD('Date'[Date], -1, YEAR) )
Explanation:
SAMEPERIODLASTYEAR → simpler, fixed 1-year shift
DATEADD → more flexible (can shift months, quarters, years)
 7. Quarter-to-Date (QTD)
Question: How to calculate QTD sales?
Answer:
Sales QTD =
TOTALQTD(
[Sales],
'Date'[Date]
)
8. MTD (Month-to-Date)
Question: How to calculate MTD?
Answer:
Sales MTD =
TOTALMTD(
[Sales],
'Date'[Date]
)
9. Custom Financial Year (April–March)
Question: How to handle Indian financial year?
Answer:
Sales FYTD =
TOTALYTD(
[Sales],
'Date'[Date],
"03/31"
)
Scenario:
Used in India where FY starts from April.
10. Last Non-Blank Value (Latest Sales)
Question: How to get latest available sales value?
Answer:
Latest Sales = CALCULATE( [Sales], LASTNONBLANK( 'Date'[Date], [Sales] ) )
11. Dynamic Date Selection (User Filter Based)
Question: How to calculate sales based on selected date range?
Answer:
Sales Selected Period = CALCULATE( [Sales], ALLSELECTED('Date') )
12. Running Total
Question: How to calculate cumulative sales?
Answer:
Running Total = CALCULATE( [Sales], FILTER( ALL('Date'), 'Date'[Date] <= MAX('Date'[Date]) ) )
13. Same Month Last Year (Ignoring Filters)
Question: How to ignore slicers and still get last year value?
Answer:
Sales LY Ignore Filter = CALCULATE( [Sales], SAMEPERIODLASTYEAR('Date'[Date]), ALL('Date') )
14. Days Difference Between Two Dates
Question: How to calculate difference between two dates?
Answer:
Days Diff = DATEDIFF( MIN('Date'[Date]), MAX('Date'[Date]), DAY )
15. Why Date Table is Important?
Question: Why is a Date table mandatory for Time Intelligence?
Answer:
- Continuous date range required
- Proper relationships needed
- Enables functions like TOTALYTD, DATEADD, etc.
