13. DAX: Data Analysis Expressions

13.16 Time Intelligence in Power BI with Example: FYTD, Last Year and YoY %

By Ravindra Bagale · Last updated: 6 October 2026

Quick answer: the short, self-contained version of this lesson is the guide DAX Rolling 12-Month Average.

First look at the data (before → after):

Sales FYTD = TOTALYTD([Total Sales], 'Date'[Date], "3/31")
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
YoY % = DIVIDE([Total Sales] - [Sales LY], [Sales LY])

Simple bhashet sangaycha tar, time intelligence functions compare values across periods. Requirements: a proper date table marked as a date table (Module 11), related to the fact table, and the date column from the Date table used in the functions.

Year-to-date (YTD), QTD, MTD

Sales YTD = TOTALYTD([Total Sales], 'Date'[Date])

-- Same result using CALCULATE + DATESYTD
Sales YTD v2 = CALCULATE([Total Sales], DATESYTD('Date'[Date]))

-- Indian financial year (April–March): year ends on 31 March
Sales FYTD = TOTALYTD([Total Sales], 'Date'[Date], "3/31")

Orders QTD = TOTALQTD([Total Orders], 'Date'[Date])
Orders MTD = TOTALMTD([Total Orders], 'Date'[Date])

The optional year-end argument is a text date (month/day). Its interpretation can depend on locale; "3/31" is the common way to express 31 March.

Previous year and previous period

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

Sales LY (DATEADD) = CALCULATE([Total Sales], DATEADD('Date'[Date], -1, YEAR))

Orders Previous Month = CALCULATE([Total Orders], DATEADD('Date'[Date], -1, MONTH))

Orders Previous Week = CALCULATE([Total Orders], DATEADD('Date'[Date], -7, DAY))

-- Full previous year, regardless of which month is selected
Sales Full Prev Year = CALCULATE([Total Sales], PARALLELPERIOD('Date'[Date], -1, YEAR))

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

Festival Orders = CALCULATE([Total Orders], 'Date'[Is Festival Day] = TRUE())

-- Use in a visual or card filtered to one Year
Diwali Orders LY =
VAR PrevYear = MAX('Date'[Year]) - 1
RETURN
    CALCULATE(
        [Total Orders],
        'Date'[Festival] = "Diwali",
        'Date'[Year] = PrevYear
    )

Year-over-Year and Month-over-Month growth %

YoY Growth % =
VAR CurrentSales = [Total Sales]
VAR PreviousSales = [Sales LY]
RETURN
    IF(
        NOT ISBLANK(CurrentSales) && NOT ISBLANK(PreviousSales),
        DIVIDE(CurrentSales - PreviousSales, PreviousSales)
    )

MoM Orders Growth % =
DIVIDE([Total Orders] - [Orders Previous Month], [Orders Previous Month])

Running total (cumulative)

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

For each point on the axis, it counts orders from the first date in the model up to the last visible date. A YTD measure is a running total (आतापर्यंतची एकत्रित बेरीज) that restarts every year. (Because orders are counted with DISTINCTCOUNT, each Order ID is counted once even across dates.)

Moving (rolling) average

-- Average of the last 3 months' sales (monthly level visual)
Sales 3M Moving Avg =
VAR Period =
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH)
RETURN
    CALCULATE(
        AVERAGEX(VALUES('Date'[Year Month]), [Total Sales]),
        Period
    )

-- 7-day moving average of daily orders (daily level visual)
Orders 7D Moving Avg =
CALCULATE(
    AVERAGEX(VALUES('Date'[Date]), [Total Orders]),
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -7, DAY)
)

DATESINPERIOD(dates, start_date, number_of_intervals, interval) returns a continuous set of dates going backwards (negative number) or forwards from the start date. Moving averages smooth out short-term ups and downs (for example weekend peaks in grocery orders) so the trend is easier to see.

Ghabru naka, time intelligence suruvatila khup functions vattat. Pan sagle ekach niyamavar chaltat – marked Date table.

Ravi Sir's Cards for this lesson

Revision card

मराठी cards