The 20 examples below showcase the types of DAX expressions and analytical solutions I develop when translating real-world business requirements into Power BI reports and dashboards. They cover a broad range of techniques, from time intelligence, rankings, and financial calculations to advanced filter context, relationship management, dynamic measures, customer analytics, and more sophisticated analytical patterns. Each example includes the DAX expression along with a technical explanation of how and why it works, providing a practical demonstration of my ability to develop, troubleshoot, and apply DAX to solve complex business intelligence problems.
Year-Over-Year (YoY) Growth %
Calculates the percentage change in sales compared to the same period last year.
YoY Growth % =
VAR CurrentSales = [Total Sales]
VAR PreviousSales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(‘Date'[Date]))
RETURN
DIVIDE(CurrentSales – PreviousSales, PreviousSales, 0)
Uses VAR to store values for readability. SAMEPERIODLASTYEAR shifts the filter context back one year, and DIVIDE handles the “division by zero” error if there were no sales last year.
Rolling 12-Month Average
Useful for smoothing out seasonality in charts.
Rolling 12M Sales =
CALCULATE(
AVERAGEX(VALUES(‘Date'[MonthYear]), [Total Sales]),
DATESINPERIOD(‘Date'[Date], MAX(‘Date'[Date]), -12, MONTH)
)
DATESINPERIOD creates a sliding window of the last 12 months. AVERAGEX iterates over that window to calculate the mean.
Dynamic Top N + “Others”
This is highly impressive in reports. It shows the Top 5 categories and groups everything else into a category called “Others.”
Top 5 and Others =
VAR TopNLimit = 5
VAR TopProducts = TOPN(TopNLimit, ALL(‘Product'[Name]), [Total Sales])
VAR OtherSales = CALCULATE([Total Sales], EXCEPT(ALL(‘Product'[Name]), TopProducts))
VAR CurrentProduct = SELECTEDVALUE(‘Product'[Name])
RETURN
IF(
ISINSCOPE(‘Product'[Name]),
IF(CurrentProduct IN TopProducts, [Total Sales], IF(CurrentProduct = “Others”, OtherSales)),
[Total Sales]
)
Uses TOPN to find the leaders and EXCEPT to find the “rest.” It requires a dummy row in your data called “Others” to work fully.
Cumulative (Running) Total
Calculates a total that adds up as time progresses.
Running Total =
CALCULATE(
[Total Sales],
FILTER(ALLSELECTED(‘Date’), ‘Date'[Date] <= MAX(‘Date'[Date]))
)
ALLSELECTED ignores filters within the visual but respects slicers. The FILTER tells DAX to sum every date that is less than or equal to the date currently being viewed in the chart.
New Customers This Month
Identifies customers who made their first-ever purchase in the current period.
New Customers =
VAR CustomersThisPeriod = VALUES(‘Sales'[CustomerID])
VAR PastCustomers = CALCULATETABLE(VALUES(‘Sales'[CustomerID]),
FILTER(ALL(‘Date’), ‘Date'[Date] < MIN(‘Date'[Date])))
RETURN
COUNTROWS(EXCEPT(CustomersThisPeriod, PastCustomers))
EXCEPT compares two lists (current customers vs. all historical customers) and returns only the names that appear in the current list but never in the history.
Basket Analysis (Purchase Correlation)
Calculates how many customers bought Product A and also bought Product B.
Orders with Both Products =
VAR ProductA = SELECTEDVALUE(‘Product'[Name])
VAR CustomersA = CALCULATETABLE(VALUES(‘Sales'[CustomerID]), ‘Product'[Name] = ProductA)
RETURN
CALCULATE([Total Orders], INTERSECT(VALUES(‘Sales'[CustomerID]), CustomersA))
INTERSECT finds the overlap between two sets of data (customers who bought the selected item vs. those who bought others).
Handling Multiple Relationships (USERELATIONSHIP)
If you have an “Order Date” and a “Ship Date,” you can only have one active relationship. This measure forces the inactive one.
Sales by Ship Date =
CALCULATE([Total Sales], USERELATIONSHIP(‘Sales'[ShipDate], ‘Date'[Date]))
This allows you to use one Date table to drive two different views of the data without creating a second table.
Pareto Analysis (80/20 Rule)
Calculates the running percentage of total sales to identify which products drive 80% of revenue.
Pareto % =
VAR TotalSalesAll = CALCULATE([Total Sales], ALLSELECTED(‘Product’))
VAR CurrentSales = [Total Sales]
VAR RunningSum = CALCULATE([Total Sales],
FILTER(ALLSELECTED(‘Product’), [Total Sales] >= CurrentSales))
RETURN
DIVIDE(RunningSum, TotalSalesAll)
It ranks products by sales and creates a running total. When the result is <= 0.80, those are your top products.
Working Days Difference
Calculates the number of days between two dates, excluding weekends.
Working Days =
COUNTROWS(
FILTER(‘Date’,
‘Date'[Date] >= MIN(‘Sales'[OrderDate]) &&
‘Date'[Date] <= MAX(‘Sales'[ShipDate]) &&
‘Date'[IsWorkingDay] = TRUE
)
)
This assumes you have a column in your Date table flagging weekends/holidays. It’s much more accurate than a simple subtraction.
Ranking with Filters (RANKX)
Ranks items within a category while ignoring or respecting slicers.
Product Rank =
IF(ISINSCOPE(‘Product'[Name]),
RANKX(ALLSELECTED(‘Product’), [Total Sales], , DESC, Dense),
BLANK()
)
ALLSELECTED ensures the rank stays between 1 and N based on what the user has filtered. Dense ensures that if there is a tie (e.g., two #2s), the next rank is #3, not #4.
Average Sales per Day (Average of an Aggregation)
Calculates the average daily sales for a month, rather than just the average of every transaction.
Avg Daily Sales =
AVERAGEX(VALUES(‘Date'[Date]), [Total Sales])
This is a “Measure of a Measure.” It first calculates [Total Sales] for every single day, then averages those daily totals.
Percent of Parent Total
In a matrix, this shows how much a sub-category contributes to its specific parent category (not the grand total).
% of Parent =
VAR SubCategorySales = [Total Sales]
VAR ParentSales = CALCULATE([Total Sales], ALL(‘Product'[SubCategory]))
RETURN
DIVIDE(SubCategorySales, ParentSales)
ALL on the sub-category removes that specific filter, effectively “stepping up” one level in the hierarchy.
Dynamic Measure Selection (SWITCH)
Allows the user to change the chart metric (e.g., Sales vs. Profit) using a slicer.
Selected Metric =
SWITCH(SELECTEDVALUE(‘MetricPicker'[ID]),
1, [Total Sales],
2, [Total Profit],
3, [Total Quantity],
[Total Sales]
)
Works with a disconnected table (MetricPicker). It makes reports incredibly interactive and saves screen space.
Customer Churn Rate
The percentage of customers who were active in the last 6 months but have not purchased in the current month.
Churn Rate =
VAR CustomersLast6M = CALCULATE(DISTINCTCOUNT(‘Sales'[CustomerID]),
DATESINPERIOD(‘Date'[Date], MAX(‘Date'[Date]), -6, MONTH))
VAR CustomersThisM = DISTINCTCOUNT(‘Sales'[CustomerID])
VAR LostCustomers = CustomersLast6M – CustomersThisM
RETURN
DIVIDE(LostCustomers, CustomersLast6M)
A vital KPI for subscription or retail businesses to track lost momentum.
Previous Row Value (for Non-Date sequences)
Finds the value of the previous row based on an Index (useful for step-by-step processes).
Previous Step Value =
VAR CurrentIndex = SELECTEDVALUE(‘Table'[Index])
RETURN
CALCULATE([Value], FILTER(ALL(‘Table’), ‘Table'[Index] = CurrentIndex – 1))
Since DAX doesn’t have a “Previous Row” concept like Excel, we use an Index column to manually “look back.”
Weighted Average
Calculates a weighted mean (e.g., average price weighted by quantity sold).
Weighted Avg Price =
SUMX(‘Sales’, ‘Sales'[UnitPrice] * ‘Sales'[Quantity]) / SUM(‘Sales'[Quantity])
SUMX iterates row-by-row to multiply price by quantity before the final division happens.
Last Non-Blank Value
Often used for Inventory or Headcount where you want to see the “current” status, even if no activity happened today.
Current Inventory =
CALCULATE(
[Inventory Count],
LASTNONBLANK(‘Date'[Date], [Inventory Count])
)
This looks back through time and picks the most recent date where a value actually existed.
Dynamic Title String
Creates a title that updates based on what the user selects in slicers.
Report Title =
“Sales Analysis for ” & SELECTEDVALUE(‘Region'[RegionName], “All Regions”) &
” in ” & SELECTEDVALUE(‘Date'[Year], “All Years”)
Enhances UI/UX. The second argument in SELECTEDVALUE provides a default if nothing is selected.
Monthly Allocation of Annual Budget
If you have a yearly budget but a daily/monthly report, this spreads the budget evenly.
Allocated Budget =
VAR DaysInPeriod = COUNTROWS(‘Date’)
VAR TotalYearDays = CALCULATE(COUNTROWS(‘Date’), ALL(‘Date'[Month], ‘Date'[Day]))
RETURN
[Yearly Budget] * DIVIDE(DaysInPeriod, TotalYearDays)
Resolves “Granularity Mismatch” by calculating the ratio of days in the current view vs. days in the year.
Moving Average with Outlier Removal
Calculates an average but ignores any days where sales were 3 standard deviations above the mean.
Clean Moving Avg =
VAR StdDev = STDEVX.P(ALL(‘Date’), [Total Sales])
VAR Mean = AVERAGEX(ALL(‘Date’), [Total Sales])
RETURN
CALCULATE(
AVERAGEX(‘Date’, [Total Sales]),
FILTER(‘Date’, ABS([Total Sales] – Mean) <= 3 * StdDev)
)
This uses statistical functions (STDEVX.P) to identify and filter out data “noise” dynamically.