Power BI DAX Measures Library

100+ ready-to-use DAX measures for Power BI.

Need a date table? Use the Date Table Generator.

Showing 175 of 175 measures

Total Sales

Sums a revenue column across whatever filter context is currently applied.

BasicsBeginner
Total Sales =
-- Sums the Amount column from the Sales table
SUM('Sales'[Amount])

Replace 'Sales'[Amount] with your own fact table and revenue column.

Requires: A fact table with a numeric revenue/amount column.

Total Quantity

Sums the number of units sold across the current filter context.

BasicsBeginner
Total Quantity =
-- Sums the Quantity column from the Sales table
SUM('Sales'[Quantity])

Replace 'Sales'[Quantity] with your own quantity column.

Requires: A fact table with a numeric quantity column.

Total Orders

Counts each order once, even when an order has several line items.

BasicsBeginner
Total Orders =
-- DISTINCTCOUNT avoids counting one order multiple times if it has several rows
DISTINCTCOUNT('Sales'[OrderID])

Replace 'Sales'[OrderID] with your table's unique order/transaction ID column.

Requires: A fact table with an order or transaction ID column.

Distinct Count of Customers

Counts how many unique customers appear in the current filter context.

BasicsBeginner
Distinct Count of Customers =
-- Counts each customer once, no matter how many orders they placed
DISTINCTCOUNT('Sales'[CustomerID])

Replace 'Sales'[CustomerID] with your own customer key column.

Requires: A fact table with a customer ID column.

Average Order Value

Divides total revenue by the number of orders to get revenue per order.

BasicsBeginner
Average Order Value =
VAR TotalRevenue = [Total Sales]
VAR OrderCount = [Total Orders]
RETURN
    -- DIVIDE returns BLANK() instead of erroring when OrderCount is 0
    DIVIDE(TotalRevenue, OrderCount)

Depends on the Total Sales and Total Orders measures from this library - create those first, or inline their expressions.

Requires: The Total Sales and Total Orders measures.

Profit

Subtracts total cost from total revenue.

BasicsBeginner
Profit =
-- Assumes a Cost column sits alongside Amount in the same Sales table
SUM('Sales'[Amount]) - SUM('Sales'[Cost])

Replace 'Sales'[Amount] and 'Sales'[Cost] with your own revenue and cost columns.

Requires: A fact table with both a revenue column and a cost column.

Profit Margin %

Expresses profit as a percentage of total sales.

BasicsBeginner
Profit Margin % =
-- DIVIDE avoids a divide-by-zero error when Total Sales is 0
DIVIDE([Profit], [Total Sales])

Depends on the Profit and Total Sales measures from this library.

Requires: The Profit and Total Sales measures.

Year-to-Date Sales

Accumulates sales from the start of the current year up to the latest selected date.

Time IntelligenceBeginner
Year-to-Date Sales =
-- 'Date'[Date] must come from a table marked as the model's official date table
TOTALYTD([Total Sales], 'Date'[Date])

'Date' must be marked as a Date Table in Power BI (Table tools > Mark as date table), with a continuous, gap-free Date column.

Requires: A proper Date dimension table marked as the model's date table.

Quarter-to-Date Sales

Accumulates sales from the start of the current quarter up to the latest selected date.

Time IntelligenceBeginner
Quarter-to-Date Sales =
TOTALQTD([Total Sales], 'Date'[Date])

Requires the same marked Date table as Year-to-Date Sales.

Requires: A proper Date dimension table marked as the model's date table.

Month-to-Date Sales

Accumulates sales from the start of the current month up to the latest selected date.

Time IntelligenceBeginner
Month-to-Date Sales =
TOTALMTD([Total Sales], 'Date'[Date])

Requires the same marked Date table as Year-to-Date Sales.

Requires: A proper Date dimension table marked as the model's date table.

Previous Year Sales

Shifts the current filter context back exactly one year and re-evaluates sales.

Time IntelligenceIntermediate
Previous Year Sales =
-- DATEADD shifts the current filter context back a full year, whatever that context is
CALCULATE([Total Sales], DATEADD('Date'[Date], -1, YEAR))

DATEADD works with any selected period (a day, a month, a year), not just a full year, which is why it's generally preferred over PREVIOUSYEAR.

Requires: A proper Date dimension table marked as the model's date table.

Previous Month Sales

Shifts the current filter context back exactly one month and re-evaluates sales.

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

Works whether the current context is a single day, a partial month, or a full month.

Requires: A proper Date dimension table marked as the model's date table.

YoY Growth %

Compares current sales to the same period last year as a percentage change.

Time IntelligenceIntermediate
YoY Growth % =
VAR CurrentSales = [Total Sales]
VAR PriorYearSales = [Previous Year Sales]
RETURN
    DIVIDE(CurrentSales - PriorYearSales, PriorYearSales)

Depends on the Total Sales and Previous Year Sales measures from this library.

Requires: The Total Sales and Previous Year Sales measures.

MoM Growth %

Compares current sales to the previous month as a percentage change.

Time IntelligenceIntermediate
MoM Growth % =
VAR CurrentSales = [Total Sales]
VAR PriorMonthSales = [Previous Month Sales]
RETURN
    DIVIDE(CurrentSales - PriorMonthSales, PriorMonthSales)

Depends on the Total Sales and Previous Month Sales measures from this library.

Requires: The Total Sales and Previous Month Sales measures.

Rolling 3-Month Sales

Sums sales over the 3 months ending on the latest date in the current context.

Time IntelligenceAdvanced
Rolling 3-Month Sales =
-- Sums the 3 months ending on the last date visible in the current filter context
CALCULATE(
    [Total Sales],
    DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -3, MONTH)
)

Change -3 to any window length. Requires a marked, gap-free Date table so LASTDATE resolves correctly.

Requires: A proper Date dimension table marked as the model's date table.

Rolling 12-Month Sales

Sums sales over the trailing 12 months ending on the latest date in the current context.

Time IntelligenceAdvanced
Rolling 12-Month Sales =
CALCULATE(
    [Total Sales],
    DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -12, MONTH)
)

Same pattern as Rolling 3-Month Sales, just with a 12-month window - useful as a smoothed "trailing twelve months" trend line.

Requires: A proper Date dimension table marked as the model's date table.

Same Period Last Year Sales

Re-evaluates sales over the equivalent period one year earlier.

Time IntelligenceIntermediate
Same Period Last Year Sales =
-- SAMEPERIODLASTYEAR assumes the current selection is one contiguous period;
-- prefer DATEADD('Date'[Date], -1, YEAR) (see Previous Year Sales) if that isn't guaranteed
CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

Behaves like Previous Year Sales for most simple selections, but can differ for irregular, non-contiguous date selections.

Requires: A proper Date dimension table marked as the model's date table.

Rank by Sales

Ranks the current item (e.g. a product) against all others by total sales.

Ranking and Top NIntermediate
Rank by Sales =
-- ALL clears any existing Product filter so every product is ranked against every other
RANKX(ALL('Product'[ProductName]), [Total Sales], , DESC, DENSE)

Replace 'Product'[ProductName] with the column you're ranking by (product, salesperson, region, etc.).

Requires: A dimension table and the Total Sales measure.

Top 5 Products

Shows sales only for items ranked in the top 5, hiding everything else.

Ranking and Top NIntermediate
Top 5 Products =
VAR CurrentRank = [Rank by Sales]
RETURN
    -- Returns BLANK() outside the top 5, which most visuals treat as "don't show this row"
    IF(CurrentRank <= 5, [Total Sales])

Depends on the Rank by Sales measure. Change 5 to whatever N you need.

Requires: The Rank by Sales measure.

Top N Sales (Incl. Others)

Keeps the top N items visible and preserves the grand total, so a chart can add an "Others" bucket for the rest.

Ranking and Top NAdvanced
Top N Sales (Incl. Others) =
VAR TopCount = 5
VAR CurrentRank = [Rank by Sales]
RETURN
    IF(
        HASONEVALUE('Product'[ProductName]),
        IF(CurrentRank <= TopCount, [Total Sales], BLANK()),
        [Total Sales] -- keeps the grand total row intact so a chart's "Others" slice can be its remainder
    )

This measure alone hides non-top-N products at the row level while keeping the grand total correct. To actually label the rest "Others" on a chart axis, pair it with a calculated column that groups everything outside the top N under one text value.

Requires: The Rank by Sales measure. Typically paired with a calculated column for the "Others" axis label.

Rank Within Category

Ranks each product against only the other products in its own category, instead of the whole table.

Ranking and Top NAdvanced
Rank Within Category =
-- ALLEXCEPT clears every filter on Product except Category, so ranking restarts per category
RANKX(
    ALLEXCEPT('Product', 'Product'[Category]),
    [Total Sales],
    ,
    DESC,
    DENSE
)

Replace 'Product'[Category] with whatever column should define each ranking group.

Requires: A dimension table with a grouping column, and the Total Sales measure.

Sales – West Region

A basic CALCULATE example: overrides the current filter to always show one specific region.

Filters and ContextBeginner
Sales – West Region =
-- CALCULATE's second argument overrides whatever Region filter is already applied
CALCULATE([Total Sales], 'Sales'[Region] = "West")

Replace 'Sales'[Region] and "West" with your own column and value.

Requires: A column to filter on and the Total Sales measure.

Total Sales (All Regions)

Always shows the grand total across every region, even inside a Region-filtered visual.

Filters and ContextIntermediate
Total Sales (All Regions) =
-- ALL removes any filter placed on the Region column specifically
CALCULATE([Total Sales], ALL('Sales'[Region]))

Pass a whole table to ALL('Sales') instead of one column if you need to remove every filter on that table.

Requires: The Total Sales measure.

Total Sales (Keep Only Region Filter)

Clears every filter on the Sales table except whatever is applied to its Region column.

Filters and ContextIntermediate
Total Sales (Keep Only Region Filter) =
-- Removes filters from every Sales column except Region
CALCULATE([Total Sales], ALLEXCEPT('Sales', 'Sales'[Region]))

ALLEXCEPT's column arguments must belong to the same table passed as its first argument - it only clears filters on that one table's own columns.

Requires: The Total Sales measure.

Total Sales (Ignore All Filters)

Returns the absolute grand total, ignoring every filter in the report, including slicers.

Filters and ContextIntermediate
Total Sales (Ignore All Filters) =
-- REMOVEFILTERS() with no arguments clears every filter in the current context
CALCULATE([Total Sales], REMOVEFILTERS())

REMOVEFILTERS is the modern, table-or-column-agnostic replacement for ALL() when you want to clear everything.

Requires: The Total Sales measure.

% of Total Sales

Shows each row's sales as a percentage of the overall grand total.

Filters and ContextIntermediate
% of Total Sales =
VAR CurrentSales = [Total Sales]
VAR GrandTotal = CALCULATE([Total Sales], ALL('Sales'))
RETURN
    DIVIDE(CurrentSales, GrandTotal)

Format this measure as a percentage in Power BI's formatting pane.

Requires: The Total Sales measure.

% of Parent Category

Shows each product's sales as a percentage of its own category's total, not the grand total.

Filters and ContextAdvanced
% of Parent Category =
VAR CurrentSales = [Total Sales]
VAR CategoryTotal = CALCULATE([Total Sales], ALLEXCEPT('Product', 'Product'[Category]))
RETURN
    DIVIDE(CurrentSales, CategoryTotal)

Replace 'Product'[Category] with whatever column represents the "parent" level in your hierarchy.

Requires: A dimension table with a category/parent column, and the Total Sales measure.

Calendar Table (Date Dimension)

Generates a continuous calendar as a calculated table, with year, month and quarter columns already added.

Dates and CalendarIntermediate
Calendar =
ADDCOLUMNS(
    CALENDAR(DATE(2020, 1, 1), DATE(2029, 12, 31)),
    "Year", YEAR([Date]),
    "MonthNumber", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "YearMonth", FORMAT([Date], "YYYY-MM")
)

This is a calculated table, not a measure - paste it as a new table, extend the date range to cover your data, then mark it as the model's date table.

Requires: None - this creates the Date table other Time Intelligence measures depend on.

Fiscal Year Column

Adds a fiscal year column to the Date table for businesses whose year doesn't start in January.

Dates and CalendarIntermediate
Fiscal Year =
VAR FiscalYearStartMonth = 7 -- change to your fiscal year's actual start month
VAR CalendarYear = YEAR('Date'[Date])
VAR CalendarMonth = MONTH('Date'[Date])
RETURN
    IF(CalendarMonth >= FiscalYearStartMonth, CalendarYear + 1, CalendarYear)

This is a calculated column on the Date table, not a measure. Set FiscalYearStartMonth to 1 if your fiscal year matches the calendar year.

Requires: A Date table.

Fiscal Quarter Column

Adds a fiscal quarter column (Q1–Q4) that lines up with a custom fiscal year start month.

Dates and CalendarIntermediate
Fiscal Quarter =
VAR FiscalYearStartMonth = 7
VAR ShiftedMonth = MOD(MONTH('Date'[Date]) - FiscalYearStartMonth, 12)
RETURN
    "Q" & (INT(DIVIDE(ShiftedMonth, 3)) + 1)

This is a calculated column on the Date table, not a measure. Keep FiscalYearStartMonth in sync with the Fiscal Year column.

Requires: A Date table.

Is Working Day Column

Flags each date as a weekday (Monday–Friday) or not.

Dates and CalendarBeginner
Is Working Day =
VAR DayNumber = WEEKDAY('Date'[Date], 2) -- return type 2: Monday = 1 ... Sunday = 7
RETURN
    DayNumber <= 5

This is a calculated column on the Date table, not a measure. It doesn't account for public holidays.

Requires: A Date table.

Working Days in Period

Counts how many working days fall inside the current date filter.

Dates and CalendarIntermediate
Working Days in Period =
CALCULATE(
    COUNTROWS('Date'),
    'Date'[Is Working Day] = TRUE
)

Depends on the Is Working Day calculated column existing on the Date table.

Requires: The Is Working Day column.

New Customers

Counts customers whose very first purchase (ever) falls inside the current date filter.

Customers and RetentionAdvanced

Replace 'Sales'[OrderDate] with your own transaction date column. Performance can degrade on very large customer tables since it evaluates a first-purchase date per customer.

Requires: A fact table with a customer ID and an order date.

Returning Customers

Counts customers active in the period who are not first-time buyers.

Customers and RetentionIntermediate
Returning Customers =
VAR TotalCustomersInPeriod = DISTINCTCOUNT('Sales'[CustomerID])
VAR NewCustomerCount = [New Customers]
RETURN
    TotalCustomersInPeriod - NewCustomerCount

Depends on the New Customers measure from this library.

Requires: The New Customers measure.

Customer Lifetime Value

A simplified CLV: total revenue per customer across all time.

Customers and RetentionIntermediate
Customer Lifetime Value =
-- Simplified CLV: total revenue per customer, ignoring margin, churn probability and time value
VAR TotalRevenue = CALCULATE([Total Sales], ALL('Date'))
VAR CustomerCount = DISTINCTCOUNT('Sales'[CustomerID])
RETURN
    DIVIDE(TotalRevenue, CustomerCount)

A production CLV model typically also factors in profit margin, average customer lifespan and discount rate - this is a starting point, not a full model.

Requires: The Total Sales measure and a Date table.

Customer Churn Rate

The percentage of last period's customers who did not return this period.

Customers and RetentionAdvanced
Customer Churn Rate =
VAR PreviousPeriodCustomers = CALCULATETABLE(VALUES('Sales'[CustomerID]), DATEADD('Date'[Date], -1, MONTH))
VAR CurrentPeriodCustomers = VALUES('Sales'[CustomerID])
VAR ChurnedCustomers = EXCEPT(PreviousPeriodCustomers, CurrentPeriodCustomers)
VAR ChurnedCount = COUNTROWS(ChurnedCustomers)
VAR PreviousCount = COUNTROWS(PreviousPeriodCustomers)
RETURN
    DIVIDE(ChurnedCount, PreviousCount)

Defined here as month-over-month churn; swap the DATEADD interval to YEAR or QUARTER for a different churn window.

Requires: A fact table with a customer ID and a Date table.

Dynamic Chart Title

Builds a chart or card title that updates to reflect whatever single value a slicer currently has selected.

Formatting and DisplayBeginner
Dynamic Chart Title =
VAR SelectedRegion = SELECTEDVALUE('Sales'[Region], "All Regions")
RETURN
    "Sales – " & SelectedRegion

SELECTEDVALUE's second argument is the fallback text shown when zero or multiple regions are selected at once.

Requires: A column to base the title on, bound to a slicer.

Sales Color (Conditional Formatting)

Returns a hex color string driven by whether sales are on or off target, for use in Power BI's conditional formatting.

Formatting and DisplayIntermediate
Sales Color (Conditional Formatting) =
VAR CurrentSales = [Total Sales]
VAR Target = [Sales Target]
RETURN
    IF(CurrentSales >= Target, "#2E7D32", "#C62828")

Replace [Sales Target] with your own target measure, or a fixed number. Apply this measure under a visual's Conditional formatting > Font color / Background color, set to "Field value".

Requires: The Total Sales measure and a Sales Target measure or value.

KPI Status Icon

Returns an up/down/flat arrow based on year-over-year growth, for a compact KPI indicator.

Formatting and DisplayIntermediate
KPI Status Icon =
VAR CurrentSales = [Total Sales]
VAR PriorSales = [Previous Year Sales]
VAR PctChange = DIVIDE(CurrentSales - PriorSales, PriorSales)
RETURN
    SWITCH(
        TRUE(),
        PctChange >= 0.05, "▲",
        PctChange <= -0.05, "▼",
        "▬"
    )

Change the 0.05 thresholds to whatever counts as meaningfully up or down for your data.

Requires: The Total Sales and Previous Year Sales measures.

Total Sales (Hide Zero Rows)

Returns BLANK() instead of 0, so visuals can hide rows that have no activity.

Formatting and DisplayBeginner
Total Sales (Hide Zero Rows) =
VAR Result = [Total Sales]
RETURN
    -- Most Power BI visuals drop rows/columns whose value is BLANK(), but not rows that are literally 0
    IF(Result = 0, BLANK(), Result)

Wrap any measure this same way to make zero-activity rows disappear from a matrix or chart.

Requires: The Total Sales measure.

Last Refresh Date

Shows the date and time the dataset was last refreshed, rather than the current live time.

UtilityIntermediate
Last Refresh Date =
MAX('RefreshInfo'[RefreshTimestamp])

Measures using NOW() directly re-evaluate live, not at refresh time. Instead, add a one-row 'RefreshInfo' table with a calculated column RefreshTimestamp = NOW() - calculated columns are fixed at refresh time, which is what actually freezes the value.

Requires: A small helper table with a NOW()-based calculated column.

Selected Region (With Fallback)

Returns the single selected value from a slicer, or a fallback label when none or several are selected.

UtilityBeginner
Selected Region (With Fallback) =
SELECTEDVALUE('Sales'[Region], "All Regions")

Replace 'Sales'[Region] and the fallback text with your own column and label.

Requires: A column bound to a slicer.

Total Sales (Toggle Switch)

Shows or hides a measure's value based on a disconnected slicer, letting report users turn a visual's data on or off.

UtilityAdvanced
Total Sales (Toggle Switch) =
VAR ShowMeasure = SELECTEDVALUE('Toggle'[ToggleValue], 1) -- 1 = on, 0 = off
RETURN
    IF(ShowMeasure = 1, [Total Sales], BLANK())

Requires a small disconnected 'Toggle' table (e.g. two rows: On = 1, Off = 0) with ToggleValue bound to a slicer.

Requires: A disconnected Toggle table and the Total Sales measure.

Dynamic Measure Selector

Lets a report user pick which underlying measure a visual displays, using a disconnected slicer.

UtilityAdvanced
Dynamic Measure Selector =
VAR SelectedMetric = SELECTEDVALUE('MetricSelector'[Metric], "Sales")
RETURN
    SWITCH(
        SelectedMetric,
        "Sales", [Total Sales],
        "Profit", [Profit],
        "Profit Margin", [Profit Margin %],
        [Total Sales] -- default fallback
    )

Requires a small disconnected 'MetricSelector' table listing the metric names (e.g. "Sales", "Profit", "Profit Margin") bound to a slicer.

Requires: A disconnected MetricSelector table and the measures it switches between.

Total Cost

Sums the cost of goods sold across the current filter context.

BasicsBeginner
Total Cost =
-- Sums the Cost column from the Sales table
SUM('Sales'[Cost])

Replace 'Sales'[Cost] with your own unit cost or COGS column.

Requires: A fact table with a numeric cost column.

Distinct Count of Products

Counts how many unique products appear in the current filter context.

BasicsBeginner
Distinct Count of Products =
DISTINCTCOUNT('Sales'[ProductID])

Replace 'Sales'[ProductID] with your own product key column.

Requires: A fact table with a product ID column.

Average Selling Price

Divides total revenue by total units sold to get the average price per unit.

BasicsBeginner
Average Selling Price =
DIVIDE([Total Sales], [Total Quantity])

Depends on the Total Sales and Total Quantity measures from this library.

Requires: The Total Sales and Total Quantity measures.

Units per Order

Divides total units sold by the number of orders to get average basket size.

BasicsBeginner
Units per Order =
DIVIDE([Total Quantity], [Total Orders])

Depends on the Total Quantity and Total Orders measures from this library.

Requires: The Total Quantity and Total Orders measures.

Sales per Customer

Divides total revenue by the number of distinct customers.

BasicsBeginner
Sales per Customer =
DIVIDE([Total Sales], [Distinct Count of Customers])

Depends on the Total Sales and Distinct Count of Customers measures from this library.

Requires: The Total Sales and Distinct Count of Customers measures.

Discount %

Expresses total discount given as a percentage of gross sales before the discount.

BasicsBeginner
Discount % =
DIVIDE(SUM('Sales'[DiscountAmount]), SUM('Sales'[GrossAmount]))

Replace the column names with your own gross amount and discount amount columns.

Requires: A fact table with gross amount and discount amount columns.

Return Rate %

Expresses the number of returned orders as a percentage of total orders.

BasicsIntermediate
Return Rate % =
VAR ReturnedOrders = CALCULATE(DISTINCTCOUNT('Sales'[OrderID]), 'Sales'[IsReturn] = TRUE)
RETURN
    DIVIDE(ReturnedOrders, [Total Orders])

Replace 'Sales'[IsReturn] with whatever flag or negative-quantity logic marks a returned order in your data.

Requires: A fact table with a way to identify returned orders.

Previous Quarter Sales

Calculates total sales for the quarter immediately before the one in the current filter context.

Time IntelligenceIntermediate
Previous Quarter Sales =
CALCULATE([Total Sales], DATEADD('Date'[Date], -1, QUARTER))

Requires a continuous, marked Date table for DATEADD to work correctly.

Requires: A marked Date table related to the Sales table.

YoY Growth (Value)

Calculates the absolute dollar change in sales compared to the same period last year.

Time IntelligenceIntermediate
YoY Growth (Value) =
VAR CurrentSales = [Total Sales]
VAR PriorYearSales = [Previous Year Sales]
RETURN
    CurrentSales - PriorYearSales

Depends on the Total Sales and Previous Year Sales measures from this library; pair with YoY Growth % for the percentage view.

Requires: The Total Sales and Previous Year Sales measures.

MoM Growth (Value)

Calculates the absolute dollar change in sales compared to the previous month.

Time IntelligenceIntermediate
MoM Growth (Value) =
VAR CurrentSales = [Total Sales]
VAR PriorMonthSales = [Previous Month Sales]
RETURN
    CurrentSales - PriorMonthSales

Depends on the Total Sales and Previous Month Sales measures from this library; pair with MoM Growth % for the percentage view.

Requires: The Total Sales and Previous Month Sales measures.

QoQ Growth %

Calculates the percentage change in sales compared to the previous quarter.

Time IntelligenceIntermediate
QoQ Growth % =
VAR CurrentSales = [Total Sales]
VAR PriorQuarterSales = [Previous Quarter Sales]
RETURN
    DIVIDE(CurrentSales - PriorQuarterSales, PriorQuarterSales)

Depends on the Total Sales and Previous Quarter Sales measures from this library.

Requires: The Total Sales and Previous Quarter Sales measures.

Previous Year YTD Sales

Calculates year-to-date sales as of the same point in the previous year, for comparing YTD progress.

Time IntelligenceAdvanced
Previous Year YTD Sales =
CALCULATE(
    [Total Sales],
    DATESYTD(SAMEPERIODLASTYEAR('Date'[Date]))
)

Requires a marked Date table; swap DATESYTD's default year-end if your fiscal year doesn't end December 31.

Requires: A marked Date table and the Total Sales measure.

Rolling 6-Month Sales

Sums sales over the trailing six months ending on the last date in the current filter context.

Time IntelligenceAdvanced
Rolling 6-Month Sales =
CALCULATE(
    [Total Sales],
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -6, MONTH)
)

Requires a continuous, marked Date table.

Requires: A marked Date table and the Total Sales measure.

Rolling 12 Month Average

Averages monthly sales over the trailing twelve months, smoothing out seasonal spikes.

Time IntelligenceAdvanced
Rolling 12 Month Average =
VAR MonthlySales =
    ADDCOLUMNS(
        DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH),
        "@MonthSales", CALCULATE([Total Sales], DATESMTD('Date'[Date]))
    )
RETURN
    AVERAGEX(SUMMARIZE(MonthlySales, 'Date'[Year], 'Date'[Month]), [Total Sales])

Requires a continuous, marked Date table; simplify by pre-aggregating to a month table if this runs slowly on large models.

Requires: A marked Date table and the Total Sales measure.

Last 7, 30, and 90 Days Sales

Sums sales over a fixed trailing window of days from the last date with data, ignoring any date filter already applied.

Time IntelligenceIntermediate
Last 7 Days Sales =
VAR LastDate = MAX('Date'[Date])
RETURN
    CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], LastDate, -7, DAY), ALL('Date'))

Last 30 Days Sales =
VAR LastDate = MAX('Date'[Date])
RETURN
    CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], LastDate, -30, DAY), ALL('Date'))

Last 90 Days Sales =
VAR LastDate = MAX('Date'[Date])
RETURN
    CALCULATE([Total Sales], DATESINPERIOD('Date'[Date], LastDate, -90, DAY), ALL('Date'))

ALL('Date') ignores whatever date filter is active so the window is always relative to today's last loaded date; drop it if you want the window relative to the current filter instead.

Requires: A marked Date table and the Total Sales measure.

Same Day Last Week

Calculates sales for the same day of the week, one week before the current filter context.

Time IntelligenceIntermediate
Same Day Last Week =
CALCULATE([Total Sales], DATEADD('Date'[Date], -7, DAY))

Requires a continuous, marked Date table.

Requires: A marked Date table and the Total Sales measure.

Fiscal YTD Sales

Calculates year-to-date sales based on a custom fiscal year end instead of the calendar year.

Time IntelligenceAdvanced
Fiscal YTD Sales =
CALCULATE([Total Sales], DATESYTD('Date'[Date], "6/30"))

Change "6/30" to your fiscal year end date; depends on the Fiscal Year Column from this library for labeling.

Requires: A marked Date table and the Total Sales measure.

Fiscal Previous Year Sales

Calculates total sales for the entire previous fiscal year.

Time IntelligenceAdvanced
Fiscal Previous Year Sales =
CALCULATE(
    [Total Sales],
    FILTER(ALL('Date'), 'Date'[Fiscal Year] = MAX('Date'[Fiscal Year]) - 1)
)

Depends on the Fiscal Year Column calculated column from this library existing on the Date table.

Requires: The Fiscal Year Column and a marked Date table.

Cumulative Total

Accumulates sales from the start of the data through the current date filter context.

Time IntelligenceIntermediate
Cumulative Total =
CALCULATE(
    [Total Sales],
    FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date]))
)

Requires a marked Date table; add a year filter inside FILTER if the total should restart each year.

Requires: A marked Date table and the Total Sales measure.

Moving Average

Averages daily sales over the trailing 7 days ending on the current date filter context.

Time IntelligenceAdvanced
Moving Average =
VAR LastDate = MAX('Date'[Date])
VAR Last7Days = DATESINPERIOD('Date'[Date], LastDate, -7, DAY)
RETURN
    AVERAGEX(Last7Days, CALCULATE([Total Sales]))

Change -7 to whatever window length your moving average should use.

Requires: A marked Date table and the Total Sales measure.

Best Month and Worst Month

Identifies the month name with the highest and lowest total sales within the current filter context.

Time IntelligenceAdvanced
Best Month =
VAR MonthlySales = SUMMARIZE(ALLSELECTED('Date'), 'Date'[Month Name], "@Sales", [Total Sales])
RETURN
    MAXX(TOPN(1, MonthlySales, [@Sales]), 'Date'[Month Name])

Worst Month =
VAR MonthlySales = SUMMARIZE(ALLSELECTED('Date'), 'Date'[Month Name], "@Sales", [Total Sales])
RETURN
    MAXX(TOPN(1, MonthlySales, [@Sales], ASC), 'Date'[Month Name])

Replace 'Date'[Month Name] with a column that uniquely identifies each month if your data spans multiple years.

Requires: A Date table with a Month Name column and the Total Sales measure.

First Sale Date, Last Sale Date

Returns the earliest and latest dates on which a sale was recorded in the current filter context.

Time IntelligenceBeginner
First Sale Date =
CALCULATE(MIN('Sales'[OrderDate]), 'Sales'[Amount] <> 0)

Last Sale Date =
CALCULATE(MAX('Sales'[OrderDate]), 'Sales'[Amount] <> 0)

Replace 'Sales'[OrderDate] with your own transaction date column.

Requires: A fact table with an order/transaction date column.

Days Since Last Sale

Counts how many days have passed since the most recent sale in the current filter context.

Time IntelligenceIntermediate
Days Since Last Sale =
VAR LastSale = [Last Sale Date]
RETURN
    DATEDIFF(LastSale, TODAY(), DAY)

Depends on the Last Sale Date measure from this library.

Requires: The Last Sale Date measure.

Sales per Working Day

Divides total sales by the number of working days in the current filter context, for a fair daily-rate comparison across months of different lengths.

Time IntelligenceIntermediate
Sales per Working Day =
DIVIDE([Total Sales], [Working Days in Period])

Depends on the Total Sales and Working Days in Period measures from this library.

Requires: The Total Sales and Working Days in Period measures.

Remaining Days in Month

Counts how many days are left in the current month, for projecting month-end results.

Time IntelligenceIntermediate
Remaining Days in Month =
VAR MonthEnd = EOMONTH(TODAY(), 0)
RETURN
    DATEDIFF(TODAY(), MonthEnd, DAY)

Swap TODAY() for a selected date measure if the report should project from a chosen date instead of today.

Requires: None beyond a standard Date table.

Run-Rate Forecast (Projected Month-End)

Projects the full month's sales by extrapolating the current daily average across the whole month.

Time IntelligenceAdvanced
Run-Rate Forecast =
VAR DaysElapsed = DATEDIFF(STARTOFMONTH(MAX('Date'[Date])), MAX('Date'[Date]), DAY) + 1
VAR DaysInMonth = DAY(EOMONTH(MAX('Date'[Date]), 0))
VAR DailyAverage = DIVIDE([Total Sales], DaysElapsed)
RETURN
    DailyAverage * DaysInMonth

This is a naive straight-line projection - it doesn't account for known seasonality within the month.

Requires: A marked Date table and the Total Sales measure.

Compare Two Selected Periods

Uses two independent date slicers (via disconnected date tables) to compare sales between any two arbitrary periods.

Time IntelligenceAdvanced
Period A Sales =
CALCULATE(
    [Total Sales],
    REMOVEFILTERS('Date'),
    'Date'[Date] >= MIN('Period A Dates'[Date]) && 'Date'[Date] <= MAX('Period A Dates'[Date])
)

Period B Sales =
CALCULATE(
    [Total Sales],
    REMOVEFILTERS('Date'),
    'Date'[Date] >= MIN('Period B Dates'[Date]) && 'Date'[Date] <= MAX('Period B Dates'[Date])
)

'Period A Dates' and 'Period B Dates' are disconnected copies of the Date table, each with its own slicer on the report.

Requires: Two disconnected date tables, each driven by its own slicer.

Top N as % of Total

Expresses the combined sales of the top N items as a percentage of overall sales.

Ranking and Top NAdvanced
Top N as % of Total =
VAR N = 5
VAR TopItems = TOPN(N, ALLSELECTED('Product'[ProductName]), [Total Sales])
VAR TopSales = CALCULATE([Total Sales], TopItems)
RETURN
    DIVIDE(TopSales, CALCULATE([Total Sales], ALLSELECTED('Product')))

Replace N with a What-If parameter to make it slicer-driven.

Requires: A dimension table and the Total Sales measure.

Bottom N

Flags whether the current item is among the lowest N performers by sales.

Ranking and Top NAdvanced
Bottom N Sales =
VAR N = 5
VAR BottomItems = TOPN(N, ALLSELECTED('Product'[ProductName]), [Total Sales], ASC)
RETURN
    IF(NOT ISEMPTY(INTERSECT(VALUES('Product'[ProductName]), SELECTCOLUMNS(BottomItems, "@P", 'Product'[ProductName]))), [Total Sales])

TOPN's ASC order flag reverses the sort, so this reuses the same pattern as a standard Top N measure.

Requires: A dimension table and the Total Sales measure.

Pareto Cumulative %

Ranks items by sales and shows the running cumulative percentage of total sales, for building an 80/20 Pareto chart.

Ranking and Top NAdvanced
Pareto Cumulative % =
VAR CurrentSales = [Total Sales]
VAR ItemsAtOrAbove =
    FILTER(
        ALLSELECTED('Product'[ProductName]),
        CALCULATE([Total Sales]) >= CurrentSales
    )
VAR CumulativeSales = CALCULATE([Total Sales], ItemsAtOrAbove)
RETURN
    DIVIDE(CumulativeSales, CALCULATE([Total Sales], ALLSELECTED('Product')))

Sort the visual by sales descending - this measure assumes rows are consumed in that order to build the running curve.

Requires: A dimension table and the Total Sales measure.

ABC Classification

Labels each item A, B or C based on its Pareto cumulative contribution to total sales.

Ranking and Top NAdvanced
ABC Classification =
VAR CumulativePercent = [Pareto Cumulative %]
RETURN
    SWITCH(
        TRUE(),
        CumulativePercent <= 0.8, "A",
        CumulativePercent <= 0.95, "B",
        "C"
    )

Depends on the Pareto Cumulative % measure from this library; adjust the 80%/95% thresholds to match your business rules.

Requires: The Pareto Cumulative % measure.

Contribution to Growth

Shows how much of the total company-wide sales growth came from each individual item.

Ranking and Top NAdvanced
Contribution to Growth =
VAR ItemGrowth = [Total Sales] - CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
VAR TotalGrowth = CALCULATE([Total Sales], ALLSELECTED('Product')) - CALCULATE([Total Sales], ALLSELECTED('Product'), SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
    DIVIDE(ItemGrowth, TotalGrowth)

Requires a marked Date table; the result can exceed 100% or go negative for items that grew while others shrank.

Requires: A dimension table, a marked Date table, and the Total Sales measure.

Lost Customers

Counts customers who purchased in the previous period but have no purchases in the current period.

Customers and RetentionAdvanced
Lost Customers =
VAR CurrentCustomers = VALUES('Sales'[CustomerID])
VAR PriorCustomers = CALCULATETABLE(VALUES('Sales'[CustomerID]), SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
    COUNTROWS(EXCEPT(PriorCustomers, CurrentCustomers))

Swap SAMEPERIODLASTYEAR for a different offset (e.g. PARALLELPERIOD by month) to compare shorter periods.

Requires: A marked Date table and a customer ID column on the fact table.

Retention Rate

Expresses the percentage of last period's customers who also purchased in the current period.

Customers and RetentionAdvanced
Retention Rate =
VAR CurrentCustomers = VALUES('Sales'[CustomerID])
VAR PriorCustomers = CALCULATETABLE(VALUES('Sales'[CustomerID]), SAMEPERIODLASTYEAR('Date'[Date]))
VAR RetainedCustomers = COUNTROWS(INTERSECT(PriorCustomers, CurrentCustomers))
RETURN
    DIVIDE(RetainedCustomers, COUNTROWS(PriorCustomers))

This is the complement of the Lost Customers measure; both use the same prior-period customer set.

Requires: A marked Date table and a customer ID column on the fact table.

Repeat Purchase Rate

Expresses the percentage of customers who placed more than one order, out of all customers.

Customers and RetentionIntermediate
Repeat Purchase Rate =
VAR RepeatCustomers =
    COUNTROWS(
        FILTER(
            VALUES('Sales'[CustomerID]),
            CALCULATE(DISTINCTCOUNT('Sales'[OrderID])) > 1
        )
    )
RETURN
    DIVIDE(RepeatCustomers, [Distinct Count of Customers])

Depends on the Distinct Count of Customers measure from this library.

Requires: The Distinct Count of Customers measure.

Days Between Orders

Averages the number of days between a customer's consecutive orders.

Customers and RetentionAdvanced
Days Between Orders =
AVERAGEX(
    VALUES('Sales'[CustomerID]),
    VAR OrderDates = CALCULATETABLE(VALUES('Sales'[OrderDate]))
    VAR OrderCount = COUNTROWS(OrderDates)
    VAR Span = DATEDIFF(MINX(OrderDates, [OrderDate]), MAXX(OrderDates, [OrderDate]), DAY)
    RETURN
        IF(OrderCount > 1, DIVIDE(Span, OrderCount - 1))
)

Customers with only one order return BLANK and are excluded from the average by AVERAGEX.

Requires: A fact table with customer ID and order date columns.

Customer First Purchase Date

Returns the earliest order date for the customer or customers in the current filter context.

Customers and RetentionBeginner
Customer First Purchase Date =
CALCULATE(MIN('Sales'[OrderDate]), ALLEXCEPT('Sales', 'Sales'[CustomerID]))

ALLEXCEPT keeps the CustomerID filter but clears any date filter, so this always returns the true first purchase.

Requires: A fact table with customer ID and order date columns.

Active Customers (Last 90 Days)

Counts distinct customers who made at least one purchase in the trailing 90 days.

Customers and RetentionIntermediate
Active Customers (Last 90 Days) =
VAR LastDate = MAX('Date'[Date])
RETURN
    CALCULATE(
        DISTINCTCOUNT('Sales'[CustomerID]),
        DATESINPERIOD('Date'[Date], LastDate, -90, DAY),
        ALL('Date')
    )

ALL('Date') anchors the 90-day window to the last loaded date rather than whatever date filter is active.

Requires: A marked Date table and a customer ID column.

Customers with No Sales in Period

Counts customers from a full customer dimension who have zero sales in the current filter context.

Customers and RetentionIntermediate
Customers with No Sales in Period =
VAR CustomersWithSales = VALUES('Sales'[CustomerID])
VAR AllCustomers = VALUES('Customer'[CustomerID])
RETURN
    COUNTROWS(EXCEPT(AllCustomers, CustomersWithSales))

Requires a full Customer dimension table, not just the customer IDs that appear in the Sales table.

Requires: A Customer dimension table related to Sales.

RFM Scores (Recency, Frequency, Monetary)

Scores each customer on recency, frequency and monetary value, the classic three inputs for customer segmentation.

Customers and RetentionAdvanced
Recency (Days) =
DATEDIFF(CALCULATE(MAX('Sales'[OrderDate])), TODAY(), DAY)

Frequency (Orders) =
CALCULATE(DISTINCTCOUNT('Sales'[OrderID]))

Monetary (Sales) =
[Total Sales]

Combine these three measures with PERCENTRANK.INC or manual banding to assign 1-5 scores for a full RFM segmentation model.

Requires: A fact table with customer ID, order date and order ID columns.

Average Customer Tenure

Averages how many days have passed between each customer's first and most recent purchase.

Customers and RetentionAdvanced
Average Customer Tenure =
AVERAGEX(
    VALUES('Sales'[CustomerID]),
    DATEDIFF(
        CALCULATE(MIN('Sales'[OrderDate])),
        CALCULATE(MAX('Sales'[OrderDate])),
        DAY
    )
)

Customers with a single order return 0 tenure, which is included in the average.

Requires: A fact table with customer ID and order date columns.

Budget vs Actual (Value and %)

Compares actual sales to budgeted sales, showing both the dollar gap and the percentage achieved.

FinanceIntermediate
Budget vs Actual (Value) =
[Total Sales] - SUM('Budget'[BudgetAmount])

Budget vs Actual (%) =
DIVIDE([Total Sales], SUM('Budget'[BudgetAmount]))

Requires a separate Budget table, typically related to the Date and Product/Region tables at whatever grain the budget was set.

Requires: A Budget table with a BudgetAmount column.

Forecast vs Actual

Compares actual sales to a previously saved forecast value.

FinanceIntermediate
Forecast vs Actual =
[Total Sales] - SUM('Forecast'[ForecastAmount])

Requires a separate Forecast table with a saved forecast value per period, distinct from a live run-rate projection.

Requires: A Forecast table with a ForecastAmount column.

Variance and Variance %

Generic variance measures comparing any two values, such as actual against budget or this year against last year.

FinanceIntermediate
Variance =
[Total Sales] - SUM('Budget'[BudgetAmount])

Variance % =
DIVIDE([Variance], SUM('Budget'[BudgetAmount]))

Swap the two sides of the subtraction to compare any pair of measures, not just sales against budget.

Requires: Two comparable values in the model, such as actual and budget.

Opening Balance and Closing Balance

Calculates the account or inventory balance at the start and end of the current filter period.

FinanceAdvanced
Opening Balance =
CALCULATE([Total Sales], DATESBETWEEN('Date'[Date], BLANK(), STARTOFMONTH(MIN('Date'[Date])) - 1))

Closing Balance =
CALCULATE([Total Sales], DATESBETWEEN('Date'[Date], BLANK(), MAX('Date'[Date])))

This treats the balance as a cumulative sum of transactions; use LASTNONBLANK instead if the fact table already stores a running balance snapshot.

Requires: A marked Date table and a transaction amount measure.

Running Balance

Accumulates a transaction amount into a running balance up to the current date filter context.

FinanceIntermediate
Running Balance =
CALCULATE(
    SUM('Ledger'[Amount]),
    FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date]))
)

Replace 'Ledger'[Amount] with your transaction amount column, using negative values for debits if needed.

Requires: A marked Date table and a transaction ledger table.

Currency Conversion (Selected Currency)

Converts an amount into whichever currency is chosen on a slicer, using a currency rates table.

FinanceAdvanced
Sales in Selected Currency =
VAR SelectedCurrency = SELECTEDVALUE('Currency'[CurrencyCode], "USD")
VAR Rate =
    CALCULATE(
        MAX('ExchangeRates'[Rate]),
        'ExchangeRates'[CurrencyCode] = SelectedCurrency
    )
RETURN
    [Total Sales] * Rate

'ExchangeRates' should hold one rate per currency (and ideally per date, joined on the Date table, for historical accuracy).

Requires: A Currency slicer table and an ExchangeRates table.

Aging Buckets (0-30, 31-60, 61-90, 90+)

Buckets an outstanding invoice amount into standard aging ranges based on days overdue.

FinanceIntermediate
Aging Bucket =
VAR DaysOverdue = DATEDIFF('Invoices'[DueDate], TODAY(), DAY)
RETURN
    SWITCH(
        TRUE(),
        DaysOverdue <= 0, "Not Due",
        DaysOverdue <= 30, "0-30",
        DaysOverdue <= 60, "31-60",
        DaysOverdue <= 90, "61-90",
        "90+"
    )

This is written as a calculated column so it can be used to slice and group invoices by bucket in a visual.

Requires: An Invoices table with a DueDate column.

Overdue Amount

Sums the amount of invoices whose due date has already passed.

FinanceBeginner
Overdue Amount =
CALCULATE(
    SUM('Invoices'[Amount]),
    'Invoices'[DueDate] < TODAY(),
    'Invoices'[Status] <> "Paid"
)

Replace 'Invoices'[Status] with whatever column marks an invoice as settled in your data.

Requires: An Invoices table with DueDate, Amount and Status columns.

DSO (Days Sales Outstanding)

Estimates the average number of days it takes to collect payment after a sale.

FinanceAdvanced
DSO (Days Sales Outstanding) =
VAR EndingReceivables = [Overdue Amount] + CALCULATE(SUM('Invoices'[Amount]), 'Invoices'[Status] <> "Paid", 'Invoices'[DueDate] >= TODAY())
VAR PeriodDays = DATEDIFF(MIN('Date'[Date]), MAX('Date'[Date]), DAY) + 1
RETURN
    DIVIDE(EndingReceivables, [Total Sales]) * PeriodDays

Depends on the Overdue Amount and Total Sales measures from this library; a simplified DSO for periods with roughly even sales.

Requires: An Invoices table and the Total Sales measure.

Weighted Average Price

Averages the unit price weighted by quantity sold, instead of a simple unweighted average of prices.

FinanceIntermediate
Weighted Average Price =
DIVIDE(
    SUMX('Sales', 'Sales'[UnitPrice] * 'Sales'[Quantity]),
    SUM('Sales'[Quantity])
)

This is mathematically the same as Average Selling Price when price and quantity come from the same rows - use whichever name fits your report.

Requires: A fact table with unit price and quantity columns.

Cumulative Budget

Accumulates the budgeted amount from the start of the year through the current date filter context.

FinanceIntermediate
Cumulative Budget =
CALCULATE(
    SUM('Budget'[BudgetAmount]),
    FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date]))
)

Add a year filter inside FILTER if the cumulative total should restart each fiscal year.

Requires: A marked Date table and a Budget table.

Stock on Hand

Sums the current on-hand inventory quantity across the current filter context.

Inventory and OperationsBeginner
Stock on Hand =
SUM('Inventory'[QuantityOnHand])

Replace 'Inventory'[QuantityOnHand] with your own on-hand quantity column.

Requires: An Inventory table with an on-hand quantity column.

Days of Supply

Estimates how many days current stock will last, based on the recent average daily sales rate.

Inventory and OperationsIntermediate
Days of Supply =
VAR DailySales = DIVIDE([Last 30 Days Sales], 30)
RETURN
    DIVIDE([Stock on Hand], DailySales)

Depends on the Last 30 Days Sales and Stock on Hand measures from this library.

Requires: The Last 30 Days Sales and Stock on Hand measures.

Stock Turnover

Calculates how many times inventory is sold and replaced over the current filter period.

Inventory and OperationsIntermediate
Stock Turnover =
DIVIDE([Total Cost], AVERAGE('Inventory'[QuantityOnHand]))

Depends on the Total Cost measure from this library; higher turnover generally means inventory isn't sitting idle.

Requires: The Total Cost measure and an Inventory table.

Reorder Flag

Flags a product as needing reorder when stock on hand falls below its reorder point.

Inventory and OperationsBeginner
Reorder Flag =
IF([Stock on Hand] <= SUM('Inventory'[ReorderPoint]), "Reorder", "OK")

Depends on the Stock on Hand measure from this library and a ReorderPoint column on the Inventory table.

Requires: The Stock on Hand measure and a ReorderPoint column.

Slow-Moving Items

Flags products that have had little or no sales activity in the last 90 days despite having stock on hand.

Inventory and OperationsIntermediate
Slow-Moving Flag =
IF([Last 90 Days Sales] = 0 && [Stock on Hand] > 0, "Slow-Moving", "Active")

Depends on the Last 90 Days Sales and Stock on Hand measures from this library; adjust the threshold from 0 to a low sales count if needed.

Requires: The Last 90 Days Sales and Stock on Hand measures.

Fill Rate %

Expresses the percentage of ordered quantity that was actually fulfilled from stock.

Inventory and OperationsIntermediate
Fill Rate % =
DIVIDE(SUM('Orders'[QuantityShipped]), SUM('Orders'[QuantityOrdered]))

Replace the column names with your own ordered and shipped quantity columns.

Requires: An Orders table with ordered and shipped quantity columns.

On-Time Delivery %

Expresses the percentage of orders delivered on or before their promised delivery date.

Inventory and OperationsIntermediate
On-Time Delivery % =
VAR OnTimeOrders = CALCULATE(COUNTROWS('Orders'), 'Orders'[ActualDeliveryDate] <= 'Orders'[PromisedDeliveryDate])
RETURN
    DIVIDE(OnTimeOrders, COUNTROWS('Orders'))

Replace the column names with your own actual and promised delivery date columns.

Requires: An Orders table with actual and promised delivery date columns.

Average Lead Time

Averages the number of days between an order being placed and it being received.

Inventory and OperationsIntermediate
Average Lead Time =
AVERAGEX(
    'Orders',
    DATEDIFF('Orders'[OrderDate], 'Orders'[ReceivedDate], DAY)
)

Replace the column names with your own order date and received date columns.

Requires: An Orders table with order date and received date columns.

Backorders Count

Counts orders that could not be fully fulfilled and are waiting on additional stock.

Inventory and OperationsBeginner
Backorders Count =
CALCULATE(
    DISTINCTCOUNT('Orders'[OrderID]),
    'Orders'[Status] = "Backordered"
)

Replace 'Orders'[Status] with whatever column marks an order as backordered in your data.

Requires: An Orders table with a status column.

Capacity Utilization %

Expresses actual production or throughput as a percentage of maximum available capacity.

Inventory and OperationsIntermediate
Capacity Utilization % =
DIVIDE(SUM('Production'[ActualOutput]), SUM('Production'[MaxCapacity]))

Replace the column names with your own actual output and maximum capacity columns.

Requires: A Production table with actual output and max capacity columns.

Headcount (End of Period)

Counts active employees as of the last date in the current filter context, a semi-additive measure.

HRAdvanced
Headcount (End of Period) =
CALCULATE(
    DISTINCTCOUNT('Employee'[EmployeeID]),
    LASTDATE('Date'[Date]),
    'Employee'[HireDate] <= MAX('Date'[Date]),
    OR('Employee'[TerminationDate] > MAX('Date'[Date]), ISBLANK('Employee'[TerminationDate]))
)

This is a snapshot measure - it must never be summed across dates, only shown at the last date of whatever period is selected.

Requires: An Employee table with HireDate and TerminationDate columns.

New Hires

Counts employees whose hire date falls within the current filter context.

HRBeginner
New Hires =
CALCULATE(
    DISTINCTCOUNT('Employee'[EmployeeID]),
    USERELATIONSHIP('Date'[Date], 'Employee'[HireDate])
)

Requires an inactive relationship between the Date table and Employee[HireDate], activated here with USERELATIONSHIP.

Requires: An inactive Date-to-HireDate relationship on the Employee table.

Terminations

Counts employees whose termination date falls within the current filter context.

HRBeginner
Terminations =
CALCULATE(
    DISTINCTCOUNT('Employee'[EmployeeID]),
    USERELATIONSHIP('Date'[Date], 'Employee'[TerminationDate])
)

Requires an inactive relationship between the Date table and Employee[TerminationDate], activated here with USERELATIONSHIP.

Requires: An inactive Date-to-TerminationDate relationship on the Employee table.

Attrition Rate

Expresses terminations as a percentage of the average headcount over the period.

HRAdvanced
Attrition Rate =
DIVIDE([Terminations], [Headcount (End of Period)])

Depends on the Terminations and Headcount (End of Period) measures from this library.

Requires: The Terminations and Headcount (End of Period) measures.

Average Tenure

Averages how many years each active employee has been with the company.

HRIntermediate
Average Tenure (Years) =
AVERAGEX(
    'Employee',
    DIVIDE(DATEDIFF('Employee'[HireDate], TODAY(), DAY), 365.25)
)

Filter to active employees only (blank TerminationDate) if the report shouldn't include people who have left.

Requires: An Employee table with a HireDate column.

Absenteeism Rate

Expresses days absent as a percentage of total scheduled working days.

HRIntermediate
Absenteeism Rate =
DIVIDE(SUM('Attendance'[DaysAbsent]), SUM('Attendance'[ScheduledDays]))

Replace the column names with your own attendance tracking columns.

Requires: An Attendance table with absent and scheduled day columns.

Overtime Hours

Sums hours worked beyond standard scheduled hours across the current filter context.

HRBeginner
Overtime Hours =
SUM('Timesheet'[OvertimeHours])

Replace 'Timesheet'[OvertimeHours] with your own overtime hours column.

Requires: A Timesheet table with an overtime hours column.

Utilization %

Expresses billable or productive hours as a percentage of total available hours.

HRIntermediate
Utilization % =
DIVIDE(SUM('Timesheet'[BillableHours]), SUM('Timesheet'[AvailableHours]))

Replace the column names with your own billable and available hours columns.

Requires: A Timesheet table with billable and available hours columns.

Time to Hire

Averages the number of days between a job requisition opening and the position being filled.

HRIntermediate
Time to Hire =
AVERAGEX(
    'Requisitions',
    DATEDIFF('Requisitions'[OpenDate], 'Requisitions'[FilledDate], DAY)
)

Replace the column names with your own requisition open and filled date columns.

Requires: A Requisitions table with open date and filled date columns.

Headcount by Age Group

Buckets each employee into an age band for demographic reporting.

HRIntermediate
Age Group =
VAR Age = DATEDIFF('Employee'[DateOfBirth], TODAY(), YEAR)
RETURN
    SWITCH(
        TRUE(),
        Age < 25, "Under 25",
        Age <= 34, "25-34",
        Age <= 44, "35-44",
        Age <= 54, "45-54",
        "55+"
    )

This is written as a calculated column so it can be used to slice and group employees by age band in a visual.

Requires: An Employee table with a DateOfBirth column.

Open Tickets, Closed Tickets

Counts support tickets currently open and tickets closed within the current filter context.

Projects and SupportBeginner
Open Tickets =
CALCULATE(COUNTROWS('Tickets'), 'Tickets'[Status] = "Open")

Closed Tickets =
CALCULATE(COUNTROWS('Tickets'), 'Tickets'[Status] = "Closed")

Replace 'Tickets'[Status] with your own status column and values.

Requires: A Tickets table with a Status column.

Ticket Backlog

Counts tickets that are still open as of the last date in the current filter context, a snapshot measure.

Projects and SupportAdvanced
Ticket Backlog =
CALCULATE(
    COUNTROWS('Tickets'),
    'Tickets'[CreatedDate] <= MAX('Date'[Date]),
    OR('Tickets'[ClosedDate] > MAX('Date'[Date]), ISBLANK('Tickets'[ClosedDate]))
)

This is a snapshot measure - show it at a single date, not summed across a date range.

Requires: A Tickets table with CreatedDate and ClosedDate columns.

Average Resolution Time

Averages the number of days between a ticket being created and being closed.

Projects and SupportIntermediate
Average Resolution Time (Days) =
AVERAGEX(
    FILTER('Tickets', NOT ISBLANK('Tickets'[ClosedDate])),
    DATEDIFF('Tickets'[CreatedDate], 'Tickets'[ClosedDate], DAY)
)

Open tickets (no ClosedDate) are excluded from the average by the FILTER.

Requires: A Tickets table with CreatedDate and ClosedDate columns.

SLA Met %

Expresses the percentage of tickets resolved within their service level agreement target.

Projects and SupportIntermediate
SLA Met % =
VAR MetSLA = CALCULATE(COUNTROWS('Tickets'), 'Tickets'[ResolutionDays] <= 'Tickets'[SLATargetDays])
RETURN
    DIVIDE(MetSLA, COUNTROWS('Tickets'))

Replace the column names with your own resolution time and SLA target columns.

Requires: A Tickets table with resolution time and SLA target columns.

First Response Time

Averages the number of hours between a ticket being created and receiving its first reply.

Projects and SupportIntermediate
First Response Time (Hours) =
AVERAGEX(
    'Tickets',
    DATEDIFF('Tickets'[CreatedDateTime], 'Tickets'[FirstResponseDateTime], HOUR)
)

Replace the column names with your own created and first-response timestamp columns.

Requires: A Tickets table with created and first-response timestamp columns.

Ticket Aging Buckets

Buckets open tickets into age ranges based on how many days they've been open.

Projects and SupportIntermediate
Ticket Age Bucket =
VAR DaysOpen = DATEDIFF('Tickets'[CreatedDate], TODAY(), DAY)
RETURN
    SWITCH(
        TRUE(),
        DaysOpen <= 1, "0-1 days",
        DaysOpen <= 3, "2-3 days",
        DaysOpen <= 7, "4-7 days",
        "8+ days"
    )

This is written as a calculated column so it can be used to slice and group tickets by age bucket in a visual.

Requires: A Tickets table with a CreatedDate column.

Reopen Rate

Expresses the percentage of closed tickets that were later reopened.

Projects and SupportIntermediate
Reopen Rate =
VAR ReopenedTickets = CALCULATE(COUNTROWS('Tickets'), 'Tickets'[WasReopened] = TRUE)
VAR ClosedTickets = CALCULATE(COUNTROWS('Tickets'), 'Tickets'[Status] = "Closed")
RETURN
    DIVIDE(ReopenedTickets, ClosedTickets)

Replace 'Tickets'[WasReopened] with whatever flag or reopen-count column your ticketing system tracks.

Requires: A Tickets table with status and reopen flag columns.

Overdue Tasks

Counts tasks whose due date has passed and that are not yet marked complete.

Projects and SupportBeginner
Overdue Tasks =
CALCULATE(
    COUNTROWS('Tasks'),
    'Tasks'[DueDate] < TODAY(),
    'Tasks'[Status] <> "Complete"
)

Replace the column names with your own due date and status columns.

Requires: A Tasks table with due date and status columns.

Project Completion %

Expresses completed tasks as a percentage of all tasks in the current project filter context.

Projects and SupportIntermediate
Project Completion % =
VAR CompletedTasks = CALCULATE(COUNTROWS('Tasks'), 'Tasks'[Status] = "Complete")
RETURN
    DIVIDE(CompletedTasks, COUNTROWS('Tasks'))

Replace 'Tasks'[Status] with your own status column and completed-status value.

Requires: A Tasks table with a status column.

Sprint Velocity

Averages the number of story points completed per sprint, over the sprints in the current filter context.

Projects and SupportIntermediate
Sprint Velocity =
AVERAGEX(
    VALUES('Sprints'[SprintName]),
    CALCULATE(SUM('Tasks'[StoryPoints]), 'Tasks'[Status] = "Complete")
)

Replace the table/column names with your own sprint and story point tracking structure.

Requires: A Sprints table and a Tasks table with story points.

Burn-Down Remaining Work

Sums the remaining story points or hours of work not yet completed in the current sprint.

Projects and SupportIntermediate
Remaining Work =
CALCULATE(
    SUM('Tasks'[StoryPoints]),
    'Tasks'[Status] <> "Complete"
)

Plot this measure by day within a sprint to produce a classic burn-down chart.

Requires: A Tasks table with a story points and status column.

CTR (Click-Through Rate)

Expresses ad clicks as a percentage of ad impressions.

MarketingBeginner
CTR =
DIVIDE(SUM('Marketing'[Clicks]), SUM('Marketing'[Impressions]))

Replace the column names with your own clicks and impressions columns.

Requires: A Marketing table with clicks and impressions columns.

CPC, CPM

Calculates the average cost per click and cost per thousand impressions for ad spend.

MarketingBeginner
CPC =
DIVIDE(SUM('Marketing'[Spend]), SUM('Marketing'[Clicks]))

CPM =
DIVIDE(SUM('Marketing'[Spend]), SUM('Marketing'[Impressions])) * 1000

Replace the column names with your own spend, clicks and impressions columns.

Requires: A Marketing table with spend, clicks and impressions columns.

Conversion Rate

Expresses conversions as a percentage of clicks or visits.

MarketingBeginner
Conversion Rate =
DIVIDE(SUM('Marketing'[Conversions]), SUM('Marketing'[Clicks]))

Replace 'Marketing'[Clicks] with 'Marketing'[Visits] if you're measuring conversion from site visits instead of ad clicks.

Requires: A Marketing table with conversions and clicks columns.

CAC (Customer Acquisition Cost)

Divides total marketing spend by the number of new customers acquired.

MarketingIntermediate
CAC =
DIVIDE(SUM('Marketing'[Spend]), [New Customers])

Depends on the New Customers measure from this library.

Requires: The New Customers measure and a Marketing spend column.

Cost per Lead

Divides total marketing spend by the number of leads generated.

MarketingBeginner
Cost per Lead =
DIVIDE(SUM('Marketing'[Spend]), SUM('Marketing'[Leads]))

Replace the column names with your own spend and leads columns.

Requires: A Marketing table with spend and leads columns.

ROAS

Expresses revenue generated per dollar of ad spend, as a ratio.

MarketingBeginner
ROAS =
DIVIDE([Total Sales], SUM('Marketing'[Spend]))

Depends on the Total Sales measure from this library; a ROAS of 4 means $4 of revenue per $1 spent.

Requires: The Total Sales measure and a Marketing spend column.

Funnel Drop-off %

Expresses the percentage of users lost between one funnel stage and the next.

MarketingIntermediate
Funnel Drop-off % =
VAR StageCount = CALCULATE(COUNTROWS('FunnelEvents'), 'FunnelEvents'[Stage] = SELECTEDVALUE('Stages'[StageName]))
VAR PreviousStageCount = CALCULATE(COUNTROWS('FunnelEvents'), 'FunnelEvents'[Stage] = SELECTEDVALUE('Stages'[PreviousStageName]))
RETURN
    1 - DIVIDE(StageCount, PreviousStageCount)

Requires a Stages table listing each funnel step and its preceding step, joined to the funnel event log.

Requires: A FunnelEvents table and a Stages lookup table.

Engagement Rate

Expresses engagements (likes, comments, shares) as a percentage of impressions or reach.

MarketingBeginner
Engagement Rate =
DIVIDE(SUM('SocialPosts'[Engagements]), SUM('SocialPosts'[Impressions]))

Replace the column names with your own engagements and impressions columns.

Requires: A SocialPosts table with engagements and impressions columns.

Bounce Rate

Expresses single-page sessions as a percentage of all sessions.

MarketingBeginner
Bounce Rate =
DIVIDE(SUM('WebSessions'[BouncedSessions]), SUM('WebSessions'[TotalSessions]))

Replace the column names with your own bounced and total session columns.

Requires: A WebSessions table with bounced and total session columns.

Completeness %

Expresses non-blank values in a column as a percentage of total rows, a basic data quality check.

Data Quality and GovernanceIntermediate
Completeness % =
VAR NonBlankRows = COUNTROWS(FILTER('Sales', NOT ISBLANK('Sales'[ColumnName])))
RETURN
    DIVIDE(NonBlankRows, COUNTROWS('Sales'))

Replace 'Sales'[ColumnName] with the column whose completeness you want to check.

Requires: Any table with a column to check for blanks.

Null / Blank Count

Counts how many rows have a blank value in a given column.

Data Quality and GovernanceBeginner
Null / Blank Count =
COUNTROWS(FILTER('Sales', ISBLANK('Sales'[ColumnName])))

Replace 'Sales'[ColumnName] with the column you want to check for missing values.

Requires: Any table with a column to check for blanks.

Duplicate Count

Counts how many rows share a key value with at least one other row, flagging potential duplicates.

Data Quality and GovernanceAdvanced
Duplicate Count =
VAR RowsWithKey = COUNTROWS('Sales')
VAR DistinctKeys = DISTINCTCOUNT('Sales'[KeyColumn])
RETURN
    RowsWithKey - DistinctKeys

Replace 'Sales'[KeyColumn] with the column (or combination, using a concatenated helper column) that should be unique.

Requires: Any table with a column that's expected to be unique.

Uniqueness %

Expresses distinct values in a column as a percentage of total rows, a basic data quality check.

Data Quality and GovernanceIntermediate
Uniqueness % =
DIVIDE(DISTINCTCOUNT('Sales'[KeyColumn]), COUNTROWS('Sales'))

Replace 'Sales'[KeyColumn] with the column you're checking for uniqueness.

Requires: Any table with a column that's expected to be unique.

Validity % (Rows Passing a Rule)

Expresses rows that satisfy a validation rule as a percentage of total rows.

Data Quality and GovernanceIntermediate
Validity % =
VAR ValidRows = COUNTROWS(FILTER('Sales', 'Sales'[Amount] >= 0 && NOT ISBLANK('Sales'[CustomerID])))
RETURN
    DIVIDE(ValidRows, COUNTROWS('Sales'))

Replace the condition inside FILTER with whatever business rule defines a valid row in your data.

Requires: Any table with a rule that defines a valid row.

Rows Failing a Rule

Counts rows that fail a validation rule, the complement of the Validity % measure.

Data Quality and GovernanceBeginner
Rows Failing a Rule =
COUNTROWS(FILTER('Sales', 'Sales'[Amount] < 0 || ISBLANK('Sales'[CustomerID])))

Replace the condition inside FILTER with the inverse of whatever rule the Validity % measure uses.

Requires: Any table with a rule that defines a valid row.

Orphan Records Count

Counts fact rows whose key doesn't exist in the related dimension table, indicating a broken relationship.

Data Quality and GovernanceAdvanced
Orphan Records Count =
VAR FactKeys = VALUES('Sales'[ProductID])
VAR DimensionKeys = VALUES('Product'[ProductID])
RETURN
    COUNTROWS(EXCEPT(FactKeys, DimensionKeys))

A non-zero result usually means the dimension table is missing rows, or the fact table has bad/unmapped keys.

Requires: A fact table and its related dimension table.

Data Freshness (Days Since Last Refresh)

Counts how many days have passed since the dataset was last refreshed.

Data Quality and GovernanceIntermediate
Data Freshness (Days) =
DATEDIFF([Last Refresh Date], TODAY(), DAY)

Depends on the Last Refresh Date measure from this library.

Requires: The Last Refresh Date measure.

Freshness Status (Fresh / Stale)

Labels the dataset as Fresh or Stale based on how many days have passed since the last refresh.

Data Quality and GovernanceBeginner
Freshness Status =
IF([Data Freshness (Days)] <= 1, "Fresh", "Stale")

Depends on the Data Freshness (Days) measure from this library; adjust the 1-day threshold to match your refresh schedule.

Requires: The Data Freshness (Days) measure.

Overall DQ Score (Weighted)

Combines completeness, validity and uniqueness into one weighted data quality score.

Data Quality and GovernanceAdvanced
Overall DQ Score =
VAR CompletenessWeight = 0.4
VAR ValidityWeight = 0.4
VAR UniquenessWeight = 0.2
RETURN
    [Completeness %] * CompletenessWeight +
    [Validity %] * ValidityWeight +
    [Uniqueness %] * UniquenessWeight

Depends on the Completeness %, Validity % and Uniqueness % measures from this library; adjust the weights to match how your organization prioritizes each dimension.

Requires: The Completeness %, Validity % and Uniqueness % measures.

Median

Calculates the median value of a numeric column across the current filter context.

StatisticsBeginner
Median Order Value =
MEDIANX('Sales', 'Sales'[Amount])

Replace 'Sales'[Amount] with whatever numeric column you want the median of.

Requires: A table with a numeric column.

Percentile (P90, P95)

Calculates the 90th and 95th percentile of a numeric column, common thresholds for SLA and outlier analysis.

StatisticsIntermediate
P90 Order Value =
PERCENTILE.INC('Sales'[Amount], 0.9)

P95 Order Value =
PERCENTILE.INC('Sales'[Amount], 0.95)

PERCENTILE.INC includes the endpoints; use PERCENTILE.EXC if your analysis requires excluding them.

Requires: A table with a numeric column.

Standard Deviation

Calculates the standard deviation of a numeric column across the current filter context.

StatisticsIntermediate
Standard Deviation =
STDEVX.P('Sales', 'Sales'[Amount])

Use STDEVX.S instead of STDEVX.P if your data is a sample rather than the full population.

Requires: A table with a numeric column.

Coefficient of Variation

Expresses standard deviation as a percentage of the mean, for comparing variability across differently-scaled measures.

StatisticsIntermediate
Coefficient of Variation =
DIVIDE([Standard Deviation], AVERAGE('Sales'[Amount]))

Depends on the Standard Deviation measure from this library.

Requires: The Standard Deviation measure.

Z-Score

Calculates how many standard deviations a value is from the mean, for spotting outliers.

StatisticsAdvanced
Z-Score =
VAR CurrentValue = [Total Sales]
VAR Mean = CALCULATE(AVERAGE('Sales'[Amount]), ALLSELECTED('Sales'))
VAR StdDev = CALCULATE(STDEVX.P('Sales', 'Sales'[Amount]), ALLSELECTED('Sales'))
RETURN
    DIVIDE(CurrentValue - Mean, StdDev)

ALLSELECTED anchors the mean and standard deviation to the report's overall selection rather than the current row context.

Requires: A table with a numeric column.

Outlier Flag

Flags rows whose Z-Score falls outside a set threshold as statistical outliers.

StatisticsAdvanced
Outlier Flag =
IF(ABS([Z-Score]) > 2, "Outlier", "Normal")

Depends on the Z-Score measure from this library; 2 standard deviations is a common default threshold.

Requires: The Z-Score measure.

Average of Monthly Totals

Averages sales across whole months, rather than averaging daily transaction rows.

StatisticsAdvanced
Average of Monthly Totals =
AVERAGEX(
    SUMMARIZE(ALLSELECTED('Date'), 'Date'[Year], 'Date'[Month]),
    [Total Sales]
)

This avoids the common mistake of using AVERAGE directly on a fact table, which averages individual transactions instead of monthly totals.

Requires: A marked Date table and the Total Sales measure.

Mode (Most Frequent Value)

Returns the most frequently occurring value in a column across the current filter context.

StatisticsAdvanced
Mode =
VAR ValueCounts = ADDCOLUMNS(VALUES('Sales'[Category]), "@Count", CALCULATE(COUNTROWS('Sales')))
RETURN
    MAXX(TOPN(1, ValueCounts, [@Count]), 'Sales'[Category])

Replace 'Sales'[Category] with the column you want the most frequent value of.

Requires: A table with a categorical column.

NPS (Net Promoter Score)

Calculates Net Promoter Score as the percentage of promoters minus the percentage of detractors.

StatisticsIntermediate
NPS =
VAR TotalResponses = COUNTROWS('Survey')
VAR Promoters = CALCULATE(COUNTROWS('Survey'), 'Survey'[Score] >= 9)
VAR Detractors = CALCULATE(COUNTROWS('Survey'), 'Survey'[Score] <= 6)
RETURN
    DIVIDE(Promoters - Detractors, TotalResponses)

Assumes a Survey table with a 0-10 recommendation score; format the result as a percentage or multiply by 100 for the conventional -100 to 100 scale.

Requires: A Survey table with a 0-10 score column.

CSAT %

Expresses satisfied survey responses as a percentage of all responses.

StatisticsBeginner
CSAT % =
VAR SatisfiedResponses = CALCULATE(COUNTROWS('Survey'), 'Survey'[Score] >= 4)
RETURN
    DIVIDE(SatisfiedResponses, COUNTROWS('Survey'))

Assumes a Survey table with a 1-5 satisfaction score, where 4 and 5 count as satisfied.

Requires: A Survey table with a 1-5 satisfaction score column.

Likert Scale Average

Averages numeric Likert-scale survey responses across the current filter context.

StatisticsBeginner
Likert Scale Average =
AVERAGE('Survey'[Score])

Replace 'Survey'[Score] with your own Likert-scale response column.

Requires: A Survey table with a numeric Likert-scale column.

Last Non-Blank Value (Semi-Additive)

Returns the most recent non-blank value in a date range, the standard pattern for semi-additive measures like account balances.

Advanced PatternsAdvanced
Last Non-Blank Balance =
CALCULATE(
    SUM('Ledger'[Balance]),
    LASTNONBLANK('Date'[Date], CALCULATE(SUM('Ledger'[Balance])))
)

This is the standard DAX pattern for values that shouldn't be summed across time, such as account balances or inventory snapshots.

Requires: A marked Date table and a snapshot-style fact table.

Distinct Count with a Condition

Counts distinct values of a column, but only for rows that meet a filter condition.

Advanced PatternsIntermediate
Distinct Customers (High Value Orders) =
CALCULATE(
    DISTINCTCOUNT('Sales'[CustomerID]),
    'Sales'[Amount] > 1000
)

Replace the condition and column names to count distinct values under whatever rule your analysis needs.

Requires: A fact table with the column to count distinctly and a filter column.

Cumulative Distinct Count

Counts how many distinct customers have ever purchased up to and including the current date filter context.

Advanced PatternsAdvanced
Cumulative Distinct Customers =
CALCULATE(
    DISTINCTCOUNT('Sales'[CustomerID]),
    FILTER(ALL('Date'[Date]), 'Date'[Date] <= MAX('Date'[Date]))
)

This counts a customer once even if they've purchased in multiple periods within the cumulative window.

Requires: A marked Date table and a customer ID column.

Sales for Selected Items vs All (ALLSELECTED)

Compares sales for the current row against the total across everything the user has selected on slicers, ignoring visual-level filters like the row itself.

Advanced PatternsAdvanced
Sales % of Selected =
DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED('Product')))

ALLSELECTED differs from ALL: it respects the report's slicers but ignores filters coming from the visual itself (like the current table row).

Requires: A dimension table and the Total Sales measure.

Products Bought Together (Basket Analysis)

Counts how many orders contain both a selected product and a second product, for basic market basket analysis.

Advanced PatternsAdvanced
Orders with Both Products =
VAR OrdersWithProductA = CALCULATETABLE(VALUES('Sales'[OrderID]), 'Product'[ProductName] = "Product A")
VAR OrdersWithProductB = CALCULATETABLE(VALUES('Sales'[OrderID]), 'Product'[ProductName] = "Product B")
RETURN
    COUNTROWS(INTERSECT(OrdersWithProductA, OrdersWithProductB))

Replace "Product A" and "Product B" with slicer-driven selections (via SELECTEDVALUE) to make this interactive.

Requires: A fact table with an order ID and related Product dimension.

Parent-Child Hierarchy Path

Builds a readable path string (e.g. "CEO > VP > Manager") through a self-referencing parent-child hierarchy.

Advanced PatternsAdvanced
Hierarchy Path =
PATH('Employee'[EmployeeID], 'Employee'[ManagerID])

Hierarchy Path (Names) =
VAR IDPath = PATH('Employee'[EmployeeID], 'Employee'[ManagerID])
RETURN
    CONCATENATEX(
        GENERATESERIES(1, PATHLENGTH(IDPath)),
        LOOKUPVALUE('Employee'[Name], 'Employee'[EmployeeID], VALUE(PATHITEM(IDPath, [Value]))),
        " > "
    )

PATH requires the parent-child columns to be on the same table; use Power BI's built-in Parent-Child hierarchy converter for deeper trees if this gets slow.

Requires: A self-referencing table with an ID column and a parent/manager ID column.

Dynamic Segmentation (Banding)

Buckets customers into segments using a separate banding table instead of hardcoded thresholds, so the bands can be edited without touching DAX.

Advanced PatternsAdvanced
Customer Segment =
VAR CustomerSales = [Total Sales]
RETURN
    CALCULATE(
        SELECTEDVALUE('SegmentBands'[SegmentName]),
        FILTER(
            'SegmentBands',
            CustomerSales >= 'SegmentBands'[MinValue] && CustomerSales < 'SegmentBands'[MaxValue]
        )
    )

'SegmentBands' is a small disconnected table with MinValue, MaxValue and SegmentName columns that business users can edit directly.

Requires: A disconnected SegmentBands table with MinValue, MaxValue and SegmentName columns.

What-If Parameter Scenario

Applies a user-adjustable What-If parameter (e.g. a price increase %) to a base measure for scenario modeling.

Advanced PatternsIntermediate
Sales with Price Increase =
VAR PriceIncreasePercent = SELECTEDVALUE('Price Increase %'[Price Increase % Value], 0)
RETURN
    [Total Sales] * (1 + PriceIncreasePercent)

Create the 'Price Increase %' What-If parameter from Modeling > New Parameter, then add its slicer to the report.

Requires: A What-If parameter table and the Total Sales measure.

Selected Date Range Text

Builds a text label showing the first and last date currently selected, for display in a report title or subtitle.

Formatting and DisplayIntermediate
Selected Date Range Text =
VAR StartDate = MIN('Date'[Date])
VAR EndDate = MAX('Date'[Date])
RETURN
    FORMAT(StartDate, "MMM d, yyyy") & " – " & FORMAT(EndDate, "MMM d, yyyy")

Requires at least one date to be in context (from a slicer or visual filter) for MIN/MAX to return meaningful values.

Requires: A marked Date table.

Last Refreshed Text

Builds a ready-to-display text string showing when the report data was last refreshed.

Formatting and DisplayBeginner
Last Refreshed Text =
"Data as of " & FORMAT([Last Refresh Date], "MMM d, yyyy h:mm AM/PM")

Depends on the Last Refresh Date measure from this library.

Requires: The Last Refresh Date measure.

"No Data" Message

Returns a friendly message when a measure would otherwise show blank, for use in a card visual.

Formatting and DisplayBeginner
Total Sales (With No Data Message) =
VAR Result = [Total Sales]
RETURN
    IF(ISBLANK(Result), "No data for this selection", FORMAT(Result, "$#,##0"))

Depends on the Total Sales measure from this library; the result becomes text, so it can't be used inside further numeric calculations.

Requires: The Total Sales measure.

Filter Applied Message

Builds a text summary listing which filters are currently active on a report page.

Formatting and DisplayAdvanced
Filter Applied Message =
VAR RegionFilter = SELECTEDVALUE('Sales'[Region], "All Regions")
VAR ProductFilter = SELECTEDVALUE('Product'[ProductName], "All Products")
RETURN
    "Showing: " & RegionFilter & " | " & ProductFilter

SELECTEDVALUE returns its second argument (the default) whenever more than one value is selected or no filter is applied.

Requires: The columns you want to report the filter state of.

Slicer Selection Count

Counts how many distinct values are currently selected on a slicer.

Formatting and DisplayBeginner
Slicer Selection Count =
COUNTROWS(VALUES('Product'[ProductName]))

Replace 'Product'[ProductName] with the column bound to the slicer you want to count selections for.

Requires: A dimension table with a column bound to a slicer.

Dynamic K / M / B Number Format

Formats a number as text using K, M or B suffixes depending on its magnitude, instead of showing full digits.

Formatting and DisplayIntermediate
Total Sales (K/M/B) =
VAR Value = [Total Sales]
RETURN
    SWITCH(
        TRUE(),
        ABS(Value) >= 1000000000, FORMAT(Value / 1000000000, "#,##0.0") & "B",
        ABS(Value) >= 1000000, FORMAT(Value / 1000000, "#,##0.0") & "M",
        ABS(Value) >= 1000, FORMAT(Value / 1000, "#,##0.0") & "K",
        FORMAT(Value, "#,##0")
    )

Depends on the Total Sales measure from this library; the result is text, so it displays well on cards but can't be used in further math.

Requires: The Total Sales measure.

Dynamic Format String

Switches a measure's display format (currency, percentage, whole number) based on a slicer selection, using Power BI's dynamic format strings.

Formatting and DisplayAdvanced
Selected Format =
SWITCH(
    SELECTEDVALUE('Format Options'[Format]),
    "Currency", "$#,##0",
    "Percentage", "0.0%",
    "Whole Number", "#,##0",
    "$#,##0"
)

Set this measure as the Total Sales measure's dynamic format string (right-click the measure > Format > fx, bind to a measure) rather than calling it directly.

Requires: A disconnected Format Options table and Power BI Desktop's dynamic format string feature.

Up / Down Arrow Text

Returns an up or down arrow character based on whether a measure increased or decreased versus a comparison period.

Formatting and DisplayBeginner
Trend Arrow =
VAR Change = [YoY Growth (Value)]
RETURN
    IF(Change >= 0, "▲", "▼")

Depends on the YoY Growth (Value) measure from this library; swap in any other delta measure to drive the arrow.

Requires: A measure representing the change to indicate.

Progress Bar Using REPT

Builds a text-based progress bar out of repeated block characters, for a lightweight visual inside a table cell.

Formatting and DisplayIntermediate
Progress Bar =
VAR PercentComplete = [Target vs Actual Achievement %]
VAR FilledBlocks = ROUND(PercentComplete * 10, 0)
RETURN
    REPT("█", FilledBlocks) & REPT("░", 10 - FilledBlocks)

Depends on the Target vs Actual Achievement % measure from this library; render the result in a table with a monospaced font for even spacing.

Requires: The Target vs Actual Achievement % measure.

Target vs Actual Achievement %

Expresses actual performance as a percentage of a target value.

Formatting and DisplayBeginner
Target vs Actual Achievement % =
DIVIDE([Total Sales], SUM('Target'[TargetAmount]))

Depends on the Total Sales measure from this library and a Target table with a TargetAmount column.

Requires: The Total Sales measure and a Target table.

What's in the library

175 measures across 16 categories: 50 beginner, 76 intermediate, 49 advanced. Each one has its own page with the full code, notes and requirements: 161 measures currently have a dedicated page.

About this library

Every measure here is a complete, working DAX formula using best practices: VAR/RETURN for readability, DIVIDE instead of / to avoid divide-by-zero errors, and a short comment explaining the tricky parts. Paste one into Power BI's formula bar, swap the placeholder table and column names (like 'Sales'[Amount] and 'Date'[Date]) for your own, and adjust it to fit your model.

Measures marked Advanced usually depend on other measures in this library, or on a properly configured Date table - check the Notes and Requires lines under each one first.

Related tools

Once you've written or adapted a measure, run it through the DAX Formatter to clean up the formatting. Looking for Power Query code instead? Browse the Power Query M Code Snippets.

Frequently asked questions

What is a DAX measure?

A DAX measure is a calculation, like a sum, ratio or growth rate, that's evaluated on the fly based on whatever filters are currently applied in a Power BI report - a slicer, a row in a table, a date range. Measures don't store any data themselves; they're formulas computed at query time.

Measure vs calculated column: what's the difference?

A calculated column is computed once per row when the data refreshes and is stored in the model, like a regular column. A measure is computed on demand for whatever context a visual currently shows, and recalculates every time a filter changes. Use a calculated column when you need a fixed, row-level value (like a fiscal year label); use a measure for anything that should react to filters and slicers (like a total or a percentage).

Do I need to change anything before pasting these into Power BI?

Yes. Every measure uses placeholder table and column names like 'Sales'[Amount] and 'Date'[Date]. Replace these with your own model's table and column names before using a measure, and check the Notes and Requires lines under each one for anything else it depends on.

Why do some measures reference other measures from this library?

Referencing an existing measure, like [Total Sales], instead of repeating its full formula keeps DAX easier to read and maintain. If a measure's Requires line lists another measure from this library, create that one first, or inline its expression wherever it's referenced.