This blog post explores 10 advanced DAX patterns with real-world explanations and ready-to-use code.
1. Rolling Average (Moving Average)
A rolling average smooths out short-term fluctuations in data, revealing long-term trends. It’s widely used in sales analysis, stock prices, and KPI tracking.
How It Works:
It calculates the average of a metric over a sliding window of N periods, moving forward as the date progresses.
DAX Code:
Rolling Avg 3M =
VAR CurrentDate = MAX('Date'[Date])
VAR StartDate = EDATE(CurrentDate, -2)
RETURN
CALCULATE(
   AVERAGEX(
       FILTER(ALL('Date'), 'Date'[Date] >= StartDate
              && 'Date'[Date] <= CurrentDate),
       [Total Sales]
   )
)
💡 Pro Tip: Change -2 to -5 for a 6-month rolling average. Use EDATE for month-based windows or DATESINPERIOD for day-based ones.
2. Year-Over-Year (YoY) Growth %
YoY Growth compares a metric from the current period to the same period last year. It’s one of the most commonly requested business metrics in dashboards.
DAX Code:
YoY Growth % =
VAR CurrentSales = [Total Sales]
VAR PreviousYearSales =
   CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
DIVIDE(
   CurrentSales - PreviousYearSales,
   PreviousYearSales,
   0
)
💡 Pro Tip: Always use DIVIDE() instead of the / operator to safely handle division by zero without errors.
3. Cumulative Total (Running Total)
A cumulative total sums all values from the beginning of time up to the current date. This is perfect for tracking progress toward annual targets or visualizing growth trends.
DAX Code:
Cumulative Sales =
CALCULATE(
   [Total Sales],
   FILTER(
       ALL('Date'[Date]),
       'Date'[Date] <= MAX('Date'[Date])
   )
)
💡 Pro Tip: Use ALL() to remove the existing filter context on the date column — otherwise the CALCULATE will be confined to only the current filter.
4. Pareto Analysis (80/20 Rule)
Pareto analysis identifies the top 20% of products, customers, or regions that contribute 80% of your revenue. This is a powerful DAX pattern for strategic decision-making.
DAX Code:
Cumulative % =
VAR TotalSales = CALCULATE([Total Sales], ALL('Product'))
VAR SalesUpToHere =
   CALCULATE(
       [Total Sales],
       FILTER(
           ALL('Product'),
           RANKX(ALL('Product'), [Total Sales],,DESC) <=
           RANKX(ALL('Product'), [Total Sales],,DESC)
       )
   )
RETURN DIVIDE(SalesUpToHere, TotalSales)
💡 Pro Tip: Combine with conditional formatting in Power BI visuals to highlight the top 20% automatically.
5. Dynamic Segmentation with SWITCH
Dynamic segmentation lets you categorize customers or products into buckets (like Low, Medium, High) based on calculated values — entirely driven by DAX without changing the underlying data.
DAX Code:
Customer Segment = SWITCH(    TRUE(),    [Total Sales] >= 100000, "Platinum",    [Total Sales] >= 50000, "Gold",    [Total Sales] >= 10000, "Silver",    "Bronze" )
💡 Pro Tip: SWITCH(TRUE(), …) is more readable than nested IF statements and performs better in complex branching logic.
6. Context Transition with CALCULATE
Context transition is one of the most powerful — and misunderstood — DAX concepts. When CALCULATE is used inside a row context (such as inside SUMX or ADDCOLUMNS), it converts the row context into an equivalent filter context. This enables row-by-row calculations that reference measures.
DAX Code:
Sales Rank =
RANKX(
   ALL('Product'),
   CALCULATE([Total Sales]),
   ,
   DESC,
   DENSE
)
💡 Pro Tip: Inside RANKX, CALCULATE() triggers context transition, converting each product row into a filter — allowing [Total Sales] to evaluate per product correctly.
7. Time Intelligence — MTD, QTD, YTD
Time intelligence functions enable period-based comparisons and accumulations. These are critical for financial dashboards and executive summaries that track performance over fiscal or calendar periods.
DAX Code:
Sales MTD = TOTALMTD([Total Sales], 'Date'[Date]) Sales QTD = TOTALQTD([Total Sales], 'Date'[Date]) Sales YTD = TOTALYTD([Total Sales], 'Date'[Date]) -- For fiscal year ending in June: Sales Fiscal YTD = TOTALYTD([Total Sales], 'Date'[Date], "06/30")
💡 Pro Tip: Always mark your date table using ‘Mark as Date Table’ in Power BI for time intelligence functions to work correctly.
8. Virtual Tables with SUMMARIZE & ADDCOLUMNS
Virtual tables let you build in-memory tables on the fly for complex multi-step calculations. SUMMARIZE groups data, while ADDCOLUMNS appends calculated columns to any table expression.
DAX Code:
Top 5 Product Sales =
VAR ProductTable =
   ADDCOLUMNS(
       SUMMARIZE('Sales', 'Product'[ProductName]),
       "ProductSales", [Total Sales]
   )
VAR TopProducts =
   TOPN(5, ProductTable, [ProductSales], DESC)
RETURN
SUMX(TopProducts, [ProductSales])
💡 Pro Tip: Avoid using SUMMARIZE to add extension columns — use ADDCOLUMNS over SUMMARIZE instead, as it avoids unexpected filter context issues.
9. What-If Parameter Simulation
What-If parameters let users adjust values through slicers, simulating different business scenarios. DAX can then use these slicer-driven values to recalculate measures dynamically — enabling powerful scenario planning.
DAX Code:
-- After creating a What-If parameter called 'Discount Rate' -- Power BI auto-generates: Discount Rate = GENERATESERIES(0, 0.5, 0.05) Simulated Revenue = VAR DiscountFactor = 1 - 'Discount Rate'[Discount Rate Value] RETURN Â Â Â [Total Sales] * DiscountFactor
💡 Pro Tip: Create the What-If parameter via Modeling > New Parameter in Power BI. The selected slicer value is exposed as a DAX measure automatically.
10. Many-to-Many Relationships with TREATAS
TREATAS is an advanced function that applies the values of a table expression as filters to a target table — even when no physical relationship exists. It’s essential for complex data models like shared dimension tables or many-to-many bridge patterns.
DAX Code:
Sales for Selected Region =
CALCULATE(
[Total Sales],
TREATAS(
VALUES(‘RegionBridge'[RegionID]),
‘Sales'[RegionID]
)
)
💡 Pro Tip: TREATAS is especially useful in role-playing dimension scenarios and many-to-many bridge tables where standard relationships cannot be defined.
