100+ ready-to-use DAX measures for Power BI.
Sums a revenue column across whatever filter context is currently applied.
Basics Beginner
Copy
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.
Basics Beginner
Copy
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.
Counts each order once, even when an order has several line items.
Basics Beginner
Copy
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.
Basics Beginner
Copy
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.
Divides total revenue by the number of orders to get revenue per order.
Basics Beginner
Copy
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.
Subtracts total cost from total revenue.
Basics Beginner
Copy
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.
Basics Beginner
Copy
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.
Accumulates sales from the start of the current year up to the latest selected date.
Time Intelligence Beginner
Copy
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.
Accumulates sales from the start of the current quarter up to the latest selected date.
Time Intelligence Beginner
Copy
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.
Accumulates sales from the start of the current month up to the latest selected date.
Time Intelligence Beginner
Copy
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.
Shifts the current filter context back exactly one year and re-evaluates sales.
Time Intelligence Intermediate
Copy
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.
Shifts the current filter context back exactly one month and re-evaluates sales.
Time Intelligence Intermediate
Copy
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.
Compares current sales to the same period last year as a percentage change.
Time Intelligence Intermediate
Copy
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.
Compares current sales to the previous month as a percentage change.
Time Intelligence Intermediate
Copy
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.
Sums sales over the 3 months ending on the latest date in the current context.
Time Intelligence Advanced
Copy
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.
Sums sales over the trailing 12 months ending on the latest date in the current context.
Time Intelligence Advanced
Copy
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.
Re-evaluates sales over the equivalent period one year earlier.
Time Intelligence Intermediate
Copy
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.
Ranks the current item (e.g. a product) against all others by total sales.
Ranking and Top N Intermediate
Copy
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 N Intermediate
Copy
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.
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 N Advanced
Copy
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.
Ranks each product against only the other products in its own category, instead of the whole table.
Ranking and Top N Advanced
Copy
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.
A basic CALCULATE example: overrides the current filter to always show one specific region.
Filters and Context Beginner
Copy
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.
Always shows the grand total across every region, even inside a Region-filtered visual.
Filters and Context Intermediate
Copy
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.
Clears every filter on the Sales table except whatever is applied to its Region column.
Filters and Context Intermediate
Copy
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.
Returns the absolute grand total, ignoring every filter in the report, including slicers.
Filters and Context Intermediate
Copy
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 Context Intermediate
Copy
% 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.
Shows each product's sales as a percentage of its own category's total, not the grand total.
Filters and Context Advanced
Copy
% 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.
Generates a continuous calendar as a calculated table, with year, month and quarter columns already added.
Dates and Calendar Intermediate
Copy
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.
Adds a fiscal year column to the Date table for businesses whose year doesn't start in January.
Dates and Calendar Intermediate
Copy
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.
Adds a fiscal quarter column (Q1–Q4) that lines up with a custom fiscal year start month.
Dates and Calendar Intermediate
Copy
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 Calendar Beginner
Copy
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 Calendar Intermediate
Copy
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.
Counts customers whose very first purchase (ever) falls inside the current date filter.
Customers and Retention Advanced
Copy
New Customers =
VAR CustomersInPeriod = VALUES ('Sales'[CustomerID] )
VAR FirstPurchaseByCustomer =
ADDCOLUMNS (
CustomersInPeriod,
"FirstPurchaseDate" , CALCULATE (MIN ('Sales'[OrderDate] ), ALL ('Sales' ))
)
VAR PeriodStart = MIN ('Sales'[OrderDate] )
VAR PeriodEnd = MAX ('Sales'[OrderDate] )
RETURN
COUNTROWS (
FILTER (
FirstPurchaseByCustomer,
[FirstPurchaseDate] >= PeriodStart && [FirstPurchaseDate] <= PeriodEnd
)
)
Show more
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 Retention Intermediate
Copy
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.
A simplified CLV: total revenue per customer across all time.
Customers and Retention Intermediate
Copy
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.
The percentage of last period's customers who did not return this period.
Customers and Retention Advanced
Copy
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.
Builds a chart or card title that updates to reflect whatever single value a slicer currently has selected.
Formatting and Display Beginner
Copy
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.
Returns a hex color string driven by whether sales are on or off target, for use in Power BI's conditional formatting.
Formatting and Display Intermediate
Copy
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.
Returns an up/down/flat arrow based on year-over-year growth, for a compact KPI indicator.
Formatting and Display Intermediate
Copy
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.
Returns BLANK() instead of 0, so visuals can hide rows that have no activity.
Formatting and Display Beginner
Copy
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.
Shows the date and time the dataset was last refreshed, rather than the current live time.
Utility Intermediate
Copy
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.
Returns the single selected value from a slicer, or a fallback label when none or several are selected.
Utility Beginner
Copy
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.
Shows or hides a measure's value based on a disconnected slicer, letting report users turn a visual's data on or off.
Utility Advanced
Copy
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.
Lets a report user pick which underlying measure a visual displays, using a disconnected slicer.
Utility Advanced
Copy
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.
Basics Beginner
Copy
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.
Basics Beginner
Copy
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.
Divides total revenue by total units sold to get the average price per unit.
Basics Beginner
Copy
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.
Divides total units sold by the number of orders to get average basket size.
Basics Beginner
Copy
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.
Divides total revenue by the number of distinct customers.
Basics Beginner
Copy
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.
Expresses total discount given as a percentage of gross sales before the discount.
Basics Beginner
Copy
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.
Expresses the number of returned orders as a percentage of total orders.
Basics Intermediate
Copy
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.
Calculates total sales for the quarter immediately before the one in the current filter context.
Time Intelligence Intermediate
Copy
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.
Calculates the absolute dollar change in sales compared to the same period last year.
Time Intelligence Intermediate
Copy
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.
Calculates the absolute dollar change in sales compared to the previous month.
Time Intelligence Intermediate
Copy
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.
Calculates the percentage change in sales compared to the previous quarter.
Time Intelligence Intermediate
Copy
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.
Calculates year-to-date sales as of the same point in the previous year, for comparing YTD progress.
Time Intelligence Advanced
Copy
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.
Sums sales over the trailing six months ending on the last date in the current filter context.
Time Intelligence Advanced
Copy
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.
Averages monthly sales over the trailing twelve months, smoothing out seasonal spikes.
Time Intelligence Advanced
Copy
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.
Sums sales over a fixed trailing window of days from the last date with data, ignoring any date filter already applied.
Time Intelligence Intermediate
Copy
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.
Calculates sales for the same day of the week, one week before the current filter context.
Time Intelligence Intermediate
Copy
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.
Calculates year-to-date sales based on a custom fiscal year end instead of the calendar year.
Time Intelligence Advanced
Copy
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.
Calculates total sales for the entire previous fiscal year.
Time Intelligence Advanced
Copy
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.
Accumulates sales from the start of the data through the current date filter context.
Time Intelligence Intermediate
Copy
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.
Averages daily sales over the trailing 7 days ending on the current date filter context.
Time Intelligence Advanced
Copy
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.
Identifies the month name with the highest and lowest total sales within the current filter context.
Time Intelligence Advanced
Copy
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.
Returns the earliest and latest dates on which a sale was recorded in the current filter context.
Time Intelligence Beginner
Copy
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 Intelligence Intermediate
Copy
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.
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 Intelligence Intermediate
Copy
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.
Counts how many days are left in the current month, for projecting month-end results.
Time Intelligence Intermediate
Copy
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.
Projects the full month's sales by extrapolating the current daily average across the whole month.
Time Intelligence Advanced
Copy
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.
Uses two independent date slicers (via disconnected date tables) to compare sales between any two arbitrary periods.
Time Intelligence Advanced
Copy
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.
Expresses the combined sales of the top N items as a percentage of overall sales.
Ranking and Top N Advanced
Copy
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.
Flags whether the current item is among the lowest N performers by sales.
Ranking and Top N Advanced
Copy
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.
Ranks items by sales and shows the running cumulative percentage of total sales, for building an 80/20 Pareto chart.
Ranking and Top N Advanced
Copy
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.
Labels each item A, B or C based on its Pareto cumulative contribution to total sales.
Ranking and Top N Advanced
Copy
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.
Shows how much of the total company-wide sales growth came from each individual item.
Ranking and Top N Advanced
Copy
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.
Counts customers who purchased in the previous period but have no purchases in the current period.
Customers and Retention Advanced
Copy
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.
Expresses the percentage of last period's customers who also purchased in the current period.
Customers and Retention Advanced
Copy
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.
Expresses the percentage of customers who placed more than one order, out of all customers.
Customers and Retention Intermediate
Copy
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.
Averages the number of days between a customer's consecutive orders.
Customers and Retention Advanced
Copy
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.
Returns the earliest order date for the customer or customers in the current filter context.
Customers and Retention Beginner
Copy
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.
Counts distinct customers who made at least one purchase in the trailing 90 days.
Customers and Retention Intermediate
Copy
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.
Counts customers from a full customer dimension who have zero sales in the current filter context.
Customers and Retention Intermediate
Copy
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.
Scores each customer on recency, frequency and monetary value, the classic three inputs for customer segmentation.
Customers and Retention Advanced
Copy
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.
Averages how many days have passed between each customer's first and most recent purchase.
Customers and Retention Advanced
Copy
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.
Compares actual sales to budgeted sales, showing both the dollar gap and the percentage achieved.
Finance Intermediate
Copy
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.
Compares actual sales to a previously saved forecast value.
Finance Intermediate
Copy
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.
Generic variance measures comparing any two values, such as actual against budget or this year against last year.
Finance Intermediate
Copy
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.
Calculates the account or inventory balance at the start and end of the current filter period.
Finance Advanced
Copy
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.
Accumulates a transaction amount into a running balance up to the current date filter context.
Finance Intermediate
Copy
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.
Converts an amount into whichever currency is chosen on a slicer, using a currency rates table.
Finance Advanced
Copy
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.
Buckets an outstanding invoice amount into standard aging ranges based on days overdue.
Finance Intermediate
Copy
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.
Sums the amount of invoices whose due date has already passed.
Finance Beginner
Copy
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.
Estimates the average number of days it takes to collect payment after a sale.
Finance Advanced
Copy
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.
Averages the unit price weighted by quantity sold, instead of a simple unweighted average of prices.
Finance Intermediate
Copy
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.
Accumulates the budgeted amount from the start of the year through the current date filter context.
Finance Intermediate
Copy
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.
Sums the current on-hand inventory quantity across the current filter context.
Inventory and Operations Beginner
Copy
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.
Estimates how many days current stock will last, based on the recent average daily sales rate.
Inventory and Operations Intermediate
Copy
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.
Calculates how many times inventory is sold and replaced over the current filter period.
Inventory and Operations Intermediate
Copy
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.
Flags a product as needing reorder when stock on hand falls below its reorder point.
Inventory and Operations Beginner
Copy
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.
Flags products that have had little or no sales activity in the last 90 days despite having stock on hand.
Inventory and Operations Intermediate
Copy
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.
Expresses the percentage of ordered quantity that was actually fulfilled from stock.
Inventory and Operations Intermediate
Copy
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.
Expresses the percentage of orders delivered on or before their promised delivery date.
Inventory and Operations Intermediate
Copy
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.
Averages the number of days between an order being placed and it being received.
Inventory and Operations Intermediate
Copy
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.
Counts orders that could not be fully fulfilled and are waiting on additional stock.
Inventory and Operations Beginner
Copy
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.
Expresses actual production or throughput as a percentage of maximum available capacity.
Inventory and Operations Intermediate
Copy
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.
Counts active employees as of the last date in the current filter context, a semi-additive measure.
HR Advanced
Copy
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.
Counts employees whose hire date falls within the current filter context.
HR Beginner
Copy
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.
Counts employees whose termination date falls within the current filter context.
HR Beginner
Copy
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.
Expresses terminations as a percentage of the average headcount over the period.
HR Advanced
Copy
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.
Averages how many years each active employee has been with the company.
HR Intermediate
Copy
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.
Expresses days absent as a percentage of total scheduled working days.
HR Intermediate
Copy
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.
Sums hours worked beyond standard scheduled hours across the current filter context.
HR Beginner
Copy
Overtime Hours =
SUM ('Timesheet'[OvertimeHours] )
Replace 'Timesheet'[OvertimeHours] with your own overtime hours column.
Requires: A Timesheet table with an overtime hours column.
Expresses billable or productive hours as a percentage of total available hours.
HR Intermediate
Copy
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.
Averages the number of days between a job requisition opening and the position being filled.
HR Intermediate
Copy
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.
Buckets each employee into an age band for demographic reporting.
HR Intermediate
Copy
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.
Counts support tickets currently open and tickets closed within the current filter context.
Projects and Support Beginner
Copy
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.
Counts tickets that are still open as of the last date in the current filter context, a snapshot measure.
Projects and Support Advanced
Copy
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.
Averages the number of days between a ticket being created and being closed.
Projects and Support Intermediate
Copy
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.
Expresses the percentage of tickets resolved within their service level agreement target.
Projects and Support Intermediate
Copy
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.
Averages the number of hours between a ticket being created and receiving its first reply.
Projects and Support Intermediate
Copy
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.
Buckets open tickets into age ranges based on how many days they've been open.
Projects and Support Intermediate
Copy
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.
Expresses the percentage of closed tickets that were later reopened.
Projects and Support Intermediate
Copy
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.
Counts tasks whose due date has passed and that are not yet marked complete.
Projects and Support Beginner
Copy
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.
Expresses completed tasks as a percentage of all tasks in the current project filter context.
Projects and Support Intermediate
Copy
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.
Averages the number of story points completed per sprint, over the sprints in the current filter context.
Projects and Support Intermediate
Copy
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.
Sums the remaining story points or hours of work not yet completed in the current sprint.
Projects and Support Intermediate
Copy
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.
Expresses ad clicks as a percentage of ad impressions.
Marketing Beginner
Copy
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.
Calculates the average cost per click and cost per thousand impressions for ad spend.
Marketing Beginner
Copy
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.
Expresses conversions as a percentage of clicks or visits.
Marketing Beginner
Copy
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.
Divides total marketing spend by the number of new customers acquired.
Marketing Intermediate
Copy
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.
Marketing Beginner
Copy
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.
Expresses revenue generated per dollar of ad spend, as a ratio.
Marketing Beginner
Copy
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.
Expresses the percentage of users lost between one funnel stage and the next.
Marketing Intermediate
Copy
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.
Expresses engagements (likes, comments, shares) as a percentage of impressions or reach.
Marketing Beginner
Copy
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.
Expresses single-page sessions as a percentage of all sessions.
Marketing Beginner
Copy
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.
Expresses non-blank values in a column as a percentage of total rows, a basic data quality check.
Data Quality and Governance Intermediate
Copy
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.
Counts how many rows have a blank value in a given column.
Data Quality and Governance Beginner
Copy
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.
Counts how many rows share a key value with at least one other row, flagging potential duplicates.
Data Quality and Governance Advanced
Copy
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.
Expresses distinct values in a column as a percentage of total rows, a basic data quality check.
Data Quality and Governance Intermediate
Copy
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.
Expresses rows that satisfy a validation rule as a percentage of total rows.
Data Quality and Governance Intermediate
Copy
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.
Counts rows that fail a validation rule, the complement of the Validity % measure.
Data Quality and Governance Beginner
Copy
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.
Counts fact rows whose key doesn't exist in the related dimension table, indicating a broken relationship.
Data Quality and Governance Advanced
Copy
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 Governance Intermediate
Copy
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.
Labels the dataset as Fresh or Stale based on how many days have passed since the last refresh.
Data Quality and Governance Beginner
Copy
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.
Combines completeness, validity and uniqueness into one weighted data quality score.
Data Quality and Governance Advanced
Copy
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.
Calculates the median value of a numeric column across the current filter context.
Statistics Beginner
Copy
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.
Calculates the 90th and 95th percentile of a numeric column, common thresholds for SLA and outlier analysis.
Statistics Intermediate
Copy
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.
Calculates the standard deviation of a numeric column across the current filter context.
Statistics Intermediate
Copy
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.
Expresses standard deviation as a percentage of the mean, for comparing variability across differently-scaled measures.
Statistics Intermediate
Copy
Coefficient of Variation =
DIVIDE ([Standard Deviation] , AVERAGE ('Sales'[Amount] ))
Depends on the Standard Deviation measure from this library.
Requires: The Standard Deviation measure.
Calculates how many standard deviations a value is from the mean, for spotting outliers.
Statistics Advanced
Copy
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.
Flags rows whose Z-Score falls outside a set threshold as statistical outliers.
Statistics Advanced
Copy
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.
Averages sales across whole months, rather than averaging daily transaction rows.
Statistics Advanced
Copy
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.
Returns the most frequently occurring value in a column across the current filter context.
Statistics Advanced
Copy
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.
Calculates Net Promoter Score as the percentage of promoters minus the percentage of detractors.
Statistics Intermediate
Copy
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.
Expresses satisfied survey responses as a percentage of all responses.
Statistics Beginner
Copy
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.
Averages numeric Likert-scale survey responses across the current filter context.
Statistics Beginner
Copy
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.
Returns the most recent non-blank value in a date range, the standard pattern for semi-additive measures like account balances.
Advanced Patterns Advanced
Copy
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.
Counts distinct values of a column, but only for rows that meet a filter condition.
Advanced Patterns Intermediate
Copy
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.
Counts how many distinct customers have ever purchased up to and including the current date filter context.
Advanced Patterns Advanced
Copy
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.
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 Patterns Advanced
Copy
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.
Counts how many orders contain both a selected product and a second product, for basic market basket analysis.
Advanced Patterns Advanced
Copy
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.
Builds a readable path string (e.g. "CEO > VP > Manager") through a self-referencing parent-child hierarchy.
Advanced Patterns Advanced
Copy
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.
Buckets customers into segments using a separate banding table instead of hardcoded thresholds, so the bands can be edited without touching DAX.
Advanced Patterns Advanced
Copy
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.
Applies a user-adjustable What-If parameter (e.g. a price increase %) to a base measure for scenario modeling.
Advanced Patterns Intermediate
Copy
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.
Builds a text label showing the first and last date currently selected, for display in a report title or subtitle.
Formatting and Display Intermediate
Copy
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 Display Beginner
Copy
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.
Returns a friendly message when a measure would otherwise show blank, for use in a card visual.
Formatting and Display Beginner
Copy
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.
Builds a text summary listing which filters are currently active on a report page.
Formatting and Display Advanced
Copy
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.
Counts how many distinct values are currently selected on a slicer.
Formatting and Display Beginner
Copy
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.
Formats a number as text using K, M or B suffixes depending on its magnitude, instead of showing full digits.
Formatting and Display Intermediate
Copy
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.
Switches a measure's display format (currency, percentage, whole number) based on a slicer selection, using Power BI's dynamic format strings.
Formatting and Display Advanced
Copy
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.
Returns an up or down arrow character based on whether a measure increased or decreased versus a comparison period.
Formatting and Display Beginner
Copy
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.
Builds a text-based progress bar out of repeated block characters, for a lightweight visual inside a table cell.
Formatting and Display Intermediate
Copy
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.
Expresses actual performance as a percentage of a target value.
Formatting and Display Beginner
Copy
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.