You now know how to add, replace, and remove filters. This section builds on that with five new skills: preserving existing filters, controlling exactly which filters get cleared, filtering with logic that Boolean expressions can't handle, shifting dates, and using variables to keep measures readable.
The time intelligence examples (18–23) assume the Date table has one row per day, covers every date in Sales without gaps, is marked as a date table, and has an active relationship to Sales[OrderDate]. Time intelligence functions depend on that.
Example 13: Respecting the Slicer with KEEPFILTERS
Scenario: In Example 5 you excluded Accessories, but that measure ignores a Category slicer. Now you want the exclusion to work inside whatever the user has selected.
Sales Excluding Accessories (Slicer-Aware) =
CALCULATE (
[Total Sales],
KEEPFILTERS ( Product[Category] <> "Accessories" )
)How it evaluates: Normally a filter on Product[Category] replaces the existing Category filter. KEEPFILTERS changes that: the new filter is intersected with the existing one. If the slicer has Bikes and Accessories selected, the result covers only Bikes. If the slicer selects only Bikes, the result is still Bikes. If nothing is selected, you get everything except Accessories.
Example 14: Share of Category with ALLEXCEPT
Scenario: A matrix shows Category and Subcategory. For each subcategory, the team wants its share of the parent category's sales.
Subcategory Share of Category % =
DIVIDE (
[Total Sales],
CALCULATE (
[Total Sales],
ALLEXCEPT ( Product, Product[Category] )
)
)How it evaluates: ALLEXCEPT removes every filter on the Product table except the one on Category. On a subcategory row, the Subcategory and Brand filters are cleared while Category stays, so the denominator is the whole category's sales. Filters on other tables, such as Year or Country, still apply to both numerator and denominator.
Example 15: Percent of the Visible Total with ALLSELECTED
Scenario: Users pick a few categories in a slicer. Each bar should show its share of the selected categories, not of all categories in the model.
Category Share of Selected %=
DIVIDE (
[Total Sales],
CALCULATE (
[Total Sales],
ALLSELECTED ( Product[Category] )
)
)How it evaluates: ALL would clear the Category filter completely, including the slicer. ALLSELECTED removes only the filters that come from the visual itself (the rows and columns) and restores the filters that come from outside it, such as slicers and report filters. The denominator is therefore the total of whatever categories the user selected, so the shares add up to 100% for that selection.
Example 16: Filtering with a Measure Using FILTER
Scenario: You want total sales from only those brands that individually exceed 1,000,000 in sales.
Sales from High-Volume Brands =
CALCULATE (
[Total Sales],
FILTER (
VALUES ( Product[Brand] ),
[Total Sales] > 1000000
)
)How it evaluates: A Boolean filter cannot contain a measure, so you use FILTER. It iterates the brands visible in the current context. For each brand, [Total Sales] is evaluated with context transition, and the brand is kept only if its sales exceed the threshold. The surviving brands become a filter on Product[Brand], and [Total Sales] is then calculated for them. The threshold is hard-coded here only for clarity; in a real report it would usually come from a parameter table.
Example 17: Comparing Two Columns of the Same Table
Scenario: Finance wants to know how much revenue came from order lines that were sold below cost.
Sales on Loss-Making Lines =
CALCULATE (
[Total Sales],
FILTER (
Sales,
Sales[SalesAmount] < Sales[Cost]
)
)How it evaluates: A Boolean filter argument can reference only one column, so it cannot compare SalesAmount to Cost. FILTER iterates the Sales rows that exist in the current context and keeps those where SalesAmount is less than Cost. The kept rows become the filter, and [Total Sales] adds up their amounts. Because FILTER iterates a fact table here, keep an eye on performance with very large data.
Example 18: Year-to-Date Sales
Scenario: A dashboard needs a running year-to-date figure that resets every January.
Sales YTD =
CALCULATE (
[Total Sales],
DATESYTD ( 'Date'[Date] )
)How it evaluates: DATESYTD returns the dates from January 1 of the current year through the last date visible in the filter context. CALCULATE uses that set of dates as the new Date filter. In a monthly visual, the March value covers January through March. It works for any year shown in the visual.
Example 19: Same Period Last Year
Scenario: Managers want to see this month's sales next to the same month a year ago.
Sales Same Period Last Year =
CALCULATE (
[Total Sales],
SAMEPERIODLASTYEAR ( 'Date'[Date] )
)How it evaluates: SAMEPERIODLASTYEAR takes the dates currently in context and returns the same dates shifted back one year. CALCULATE replaces the Date filter with that shifted set. In a visual showing March 2025, the measure returns March 2024 sales.
Example 20: Year-over-Year Growth % with Variables
Scenario: You need a growth percentage that is easy to read, debug, and reuse.
Sales YoY Growth % =
VAR CurrentSales = [Total Sales]
VAR PriorYearSales =
CALCULATE (
[Total Sales],
SAMEPERIODLASTYEAR ( 'Date'[Date] )
)
RETURN
DIVIDE ( CurrentSales - PriorYearSales, PriorYearSales )How it evaluates: Each variable is calculated once, in the filter context where the measure is called, and the result is stored. CurrentSales uses the visual's filters as they are. PriorYearSales uses CALCULATE to move the dates back a year. DIVIDE then returns the growth ratio, or blank if there are no prior-year sales. Variables keep the logic readable and avoid repeating the same calculation.
Example 21: Previous Month Sales with DATEADD
Scenario: A monthly report needs last month's sales for a month-over-month comparison.
Sales Previous Month =
CALCULATE (
[Total Sales],
DATEADD ( 'Date'[Date], -1, MONTH )
)How it evaluates: DATEADD shifts every date in the current context back by one month. In a visual showing April, the filter becomes March and [Total Sales] is calculated for it. You can change the interval to DAY, QUARTER, or YEAR. DATEADD works best when the visual is at a clean granularity, such as full months.
Example 22: Rolling 3-Month Sales
Scenario: To smooth out seasonality, the finance team tracks sales over the trailing three months.
Sales Rolling 3 Months =
CALCULATE (
[Total Sales],
DATESINPERIOD (
'Date'[Date],
MAX ( 'Date'[Date] ),
-3,
MONTH
)
)How it evaluates: MAX('Date'[Date]) finds the last date visible in the current context, for example the last day of the current month in a monthly visual. DATESINPERIOD then returns a window of three months ending on that date. CALCULATE applies the window as the Date filter, so the measure adds up sales for the current month and the two before it.
Example 23: Fiscal Year-to-Date
Scenario: The company's fiscal year ends on June 30. Year-to-date must restart every July 1.
Sales Fiscal YTD =
CALCULATE (
[Total Sales],
DATESYTD ( 'Date'[Date], "6-30" )
)How it evaluates: The optional second argument of DATESYTD tells DAX when the year ends. With "6-30", the accumulation starts on July 1 and runs to the last visible date. In a visual showing September, the measure covers July through September of the current fiscal year.
Example 24: Average Order Value for One Segment
Scenario: Sales leadership wants to see average order value for Corporate customers next to the overall figure.
Average Order Value =
DIVIDE ( [Total Sales], [Order Count] )
Corporate Average Order Value =
CALCULATE (
[Average Order Value],
Customer[Segment] = "Corporate"
)How it evaluates: The first measure divides sales by distinct orders. The second wraps it in CALCULATE with a Segment filter. Both [Total Sales] and [Order Count] inside the ratio are evaluated for Corporate customers only, so you get a correct segment-level ratio rather than a ratio of unrelated numbers.
Example 25: Gap to a Growth Target
Scenario: The target for this year is last year's sales plus 10%. Management wants to see how far ahead or behind each period is.
Sales Gap to Growth Target =
VAR PriorYearSales =
CALCULATE (
[Total Sales],
SAMEPERIODLASTYEAR ( 'Date'[Date] )
)
VAR TargetSales = PriorYearSales * 1.10
RETURN
[Total Sales] - TargetSalesHow it evaluates: PriorYearSales shifts the dates back a year and calculates sales. TargetSales then adds 10% on top. The final line subtracts the target from current sales: a positive result means the period is ahead of target, a negative one means it is behind. The 10% is an assumption for this example. In a real model it would usually come from a target table or a parameter.
Example 26: Share of the Grand Total Ignoring Every Filter
Scenario: Whatever the user filters, each value should be shown as a share of total sales across the whole model.
Share of Grand Total % =
DIVIDE (
[Total Sales],
CALCULATE ( [Total Sales], REMOVEFILTERS () )
)How it evaluates: Called with no arguments, REMOVEFILTERS clears every filter in the model, including slicers, report filters, and visual rows and columns. The denominator is therefore the all-time, all-products, all-customers total. Use it deliberately: unlike ALLSELECTED, it ignores the user's slicer selections, so the percentages will not add up to 100% when a slicer is active.