Menu
CoddyTech

Power BI Time Intelligence Interview Questions

Year to date, same period last year, growth, rolling windows, fiscal years and the date table they all depend on.

10 questions
  1. What do DAX time intelligence functions need to work?

    A date table with one row per day, no gaps, covering whole years, related to the fact table and marked as a date table (or related on a column of type date). The functions return a table of dates, such as the dates from 1 January to the current date, which CALCULATE applies as a filter. Without a proper date table, year to date and last year results are wrong or blank.

  2. How do you calculate year to date sales?

    With TOTALYTD, or with CALCULATE and DATESYTD, which is the same thing:

    Sales YTD = TOTALYTD ( [Total Sales], 'Date'[Date] )
    
    Sales YTD 2 = CALCULATE ( [Total Sales], DATESYTD ( 'Date'[Date] ) )
    
    Sales FYTD = TOTALYTD ( [Total Sales], 'Date'[Date], "6/30" )
    

    The last one uses a fiscal year that ends on 30 June. The CALCULATE form is handy when you need to add other filters.

  3. How do you show the same period last year?

    Sales LY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
    

    SAMEPERIODLASTYEAR shifts the dates in the current filter back one year: March 2025 becomes March 2024, and a year to date range becomes last year's range. DATEADD ( 'Date'[Date], -1, YEAR ) gives the same result and also works for months and quarters.

  4. How do you write year over year growth as a percentage?

    Sales YoY % =
    VAR Current = [Total Sales]
    VAR LastYear = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
    RETURN
        IF ( NOT ISBLANK ( LastYear ), DIVIDE ( Current - LastYear, LastYear ) )
    

    The variables keep the formula readable and compute last year once. Returning BLANK when last year is empty avoids a 100% growth on the first year of data. Format the measure as a percentage.

  5. What is the difference between DATEADD and PARALLELPERIOD?

    DATEADD shifts the dates in the current filter by an interval and keeps their shape: 1 to 10 March becomes 1 to 10 February. PARALLELPERIOD returns whole periods: with 1 to 10 March selected, PARALLELPERIOD ( 'Date'[Date], -1, MONTH ) returns all of February. Use DATEADD to compare like with like, PARALLELPERIOD (or PREVIOUSMONTH) for the full previous period.

  6. How do you calculate a rolling 12 month total?

    Sales R12M =
    CALCULATE (
        [Total Sales],
        DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH )
    )
    

    DATESINPERIOD returns the 12 months that end on the last visible date, so on a monthly chart each point is the year ending that month. It smooths seasonality, which makes trends easier to read than raw monthly values.

  7. How do you write a running total over all time?

    Remove the date filter and keep every date up to the last visible one:

    Sales Running Total =
    CALCULATE (
        [Total Sales],
        FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )
    

    MAX ( 'Date'[Date] ) is evaluated in the outer filter context, so for each month it is that month's last day. To restart each year, use DATESYTD instead.

  8. How do you write a 7 day moving average, and what is the trap?

    Sales 7 Day Avg =
    AVERAGEX (
        DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -7, DAY ),
        [Total Sales]
    )
    

    AVERAGEX skips blank values, so a day with no sales is left out and the result is the average per selling day, not per calendar day. If closed days should count as zero, divide the 7 day total by 7 instead.

  9. How do you handle a fiscal year that does not start in January?

    Add fiscal columns to the date table (fiscal year, fiscal quarter, fiscal month number for sorting) and give the year end to the year to date functions: TOTALYTD ( [Total Sales], 'Date'[Date], "3/31" ) for a year ending 31 March. Calendars that are not month based, such as 4-4-5 retail weeks, do not work with these functions; they need period columns in the date table and measures written with FILTER on those columns.

  10. Why does year to date show values in future months, and how do you stop it?

    The date table runs to the end of the year, and a year to date total for November still has all the sales up to November, so the line goes flat to December. Return blank after the last sale:

    Sales YTD to Date =
    VAR LastSale = CALCULATE ( MAX ( Sales[Order Date] ), ALL ( Sales ) )
    RETURN
        IF ( MIN ( 'Date'[Date] ) <= LastSale, TOTALYTD ( [Total Sales], 'Date'[Date] ) )
    
Coddy programming languages illustration

Learn to code with Coddy

Get started