"Reading about DAX is theory. Writing DAX against real data is the skill that gets you hired."
This is the hands-on companion to Comparative Analytics, Business Algorithms & Advanced Analytics Concepts. Every concept from that article is now implemented step by step — with a real demo dataset, exact DAX to copy, and expected outputs to validate against. Work through this in Power BI Desktop and by the end you will have a complete analytics solution built from scratch.
📦 Step 0 — Download the Demo Dataset
We use a fictional retail company — Northwind Electronics — with 3 years of sales data across 5 product categories, 8 regions, and 1,200 customers. The dataset is designed to contain intentional anomalies, seasonality, and RFM patterns so every analytics technique produces interesting, non-trivial results.
The dataset contains these tables:
🧱 Step 1 — Foundation Measures (Build These First)
Create a dedicated Measures table (Enter Data → single row, name column "Placeholder", hide the column). All measures go here to keep the model clean.
1.1 Total Revenue
Total Revenue =
SUM(Sales[Revenue])Validate: Add to a Card visual. Expected: approximately $4.2M–$6.8M depending on your data range.
1.2 Total Units Sold
Total Units =
SUM(Sales[Quantity])1.3 Total Orders
Total Orders =
DISTINCTCOUNT(Sales[OrderID])1.4 Average Order Value
Avg Order Value =
DIVIDE([Total Revenue], [Total Orders])Why DIVIDE not /: DIVIDE returns BLANK instead of an error when Total Orders is zero — critical for filtered views with no data.
1.5 Gross Margin
Gross Margin =
SUMX(
Sales,
Sales[Revenue] - (RELATED(Products[UnitCost]) * Sales[Quantity])
)Why SUMX not SUM: Margin requires a row-by-row calculation (revenue minus cost per row) before summing. SUM cannot do this — it only sums a single column.
1.6 Gross Margin %
Gross Margin % =
DIVIDE([Gross Margin], [Total Revenue])Format as Percentage with 1 decimal place.
📅 Step 2 — Comparative Analytics: Period vs Period
2.1 Revenue Last Year (SAMEPERIODLASTYEAR)
Revenue LY =
CALCULATE(
[Total Revenue],
SAMEPERIODLASTYEAR(Dates[Date])
)2.2 Year-over-Year Growth Amount
YoY Growth =
[Total Revenue] - [Revenue LY]2.3 Year-over-Year Growth %
YoY Growth % =
DIVIDE(
[Total Revenue] - [Revenue LY],
[Revenue LY]
)Format as Percentage. Validate: In a matrix with Year on rows and this measure in values — 2023 should show BLANK (no prior year), 2024 shows growth vs 2023, 2025 shows growth vs 2024.
2.4 Revenue Previous Month (DATEADD)
Revenue PM =
CALCULATE(
[Total Revenue],
DATEADD(Dates[Date], -1, MONTH)
)2.5 Month-over-Month Growth %
MoM Growth % =
DIVIDE(
[Total Revenue] - [Revenue PM],
[Revenue PM]
)2.6 Revenue vs Target
Revenue vs Target =
VAR ActualRevenue = [Total Revenue]
VAR TargetRevenue =
CALCULATE(
SUM(Targets[RevenueTarget]),
TREATAS(VALUES(Dates[Year]), Targets[Year]),
TREATAS(VALUES(Dates[Month]), Targets[Month]),
TREATAS(VALUES(Customers[Region]), Targets[Region])
)
RETURN
DIVIDE(ActualRevenue - TargetRevenue, TargetRevenue)Key concept: TREATAS maps a column from one table to another without a physical relationship — used here because Targets links on Year+Month+Region composite, not a single key.
🏆 Step 3 — Ranking Models with RANKX
3.1 Global Product Rank by Revenue
Product Revenue Rank =
RANKX(
ALL(Products[ProductName]),
[Total Revenue],
,
DESC,
Dense
)Arguments explained:
ALL(Products[ProductName])— ranks across ALL products, ignoring any visual filter[Total Revenue]— the value to rank on- Third argument (blank) — uses current context value, not a fixed value
DESC— highest revenue = rank 1Dense— no gaps (tied items get same rank, next rank is consecutive)
3.2 Rank Within Category
Category Rank =
RANKX(
ALLEXCEPT(Products, Products[Category]),
[Total Revenue],
,
DESC,
Dense
)ALLEXCEPT vs ALL: ALLEXCEPT removes all filters EXCEPT the ones specified — so ranking resets per category but ranks globally within each category.
3.3 Top 10 Flag
Is Top 10 =
IF([Product Revenue Rank] <= 10, "Top 10", "Others")3.4 Customer Revenue Rank
Customer Revenue Rank =
RANKX(
ALL(Customers[CustomerName]),
[Total Revenue],
,
DESC,
Dense
)👥 Step 4 — RFM Customer Segmentation
RFM (Recency, Frequency, Monetary) is calculated as calculated columns on the Customers table — not measures — because each customer has a fixed score that does not change with visual filters.
4.1 Last Purchase Date (Calculated Column on Customers)
Last Purchase Date =
CALCULATE(
MAX(Sales[OrderDate]),
RELATEDTABLE(Sales)
)4.2 Recency Score (Calculated Column)
Recency Score =
VAR DaysSince =
DATEDIFF(Customers[Last Purchase Date], TODAY(), DAY)
RETURN
SWITCH(TRUE(),
DaysSince <= 30, 5,
DaysSince <= 60, 4,
DaysSince <= 90, 3,
DaysSince <= 180, 2,
1
)4.3 Frequency Score (Calculated Column)
Frequency Score =
VAR OrderCount =
CALCULATE(DISTINCTCOUNT(Sales[OrderID]), RELATEDTABLE(Sales))
RETURN
SWITCH(TRUE(),
OrderCount >= 20, 5,
OrderCount >= 10, 4,
OrderCount >= 5, 3,
OrderCount >= 2, 2,
1
)4.4 Monetary Score (Calculated Column)
Monetary Score =
VAR LifetimeRevenue =
CALCULATE(SUM(Sales[Revenue]), RELATEDTABLE(Sales))
RETURN
SWITCH(TRUE(),
LifetimeRevenue >= 50000, 5,
LifetimeRevenue >= 20000, 4,
LifetimeRevenue >= 10000, 3,
LifetimeRevenue >= 2000, 2,
1
)4.5 Combined RFM Score (Calculated Column)
RFM Score =
Customers[Recency Score] + Customers[Frequency Score] + Customers[Monetary Score]4.6 Customer Segment Label (Calculated Column)
Customer Segment =
SWITCH(TRUE(),
Customers[RFM Score] >= 13, "Champions",
Customers[RFM Score] >= 10, "Loyal Customers",
Customers[RFM Score] >= 7, "Potential Loyalists",
Customers[RFM Score] >= 5, "At Risk",
"Lost"
)💰 Step 5 — Price Comparison Analytics
5.1 Average Selling Price
Avg Selling Price =
DIVIDE(
SUMX(Sales, Sales[Revenue]),
SUMX(Sales, Sales[Quantity])
)This calculates weighted average price — not a simple average of unit prices. SUMX iterates row by row.
5.2 Market Benchmark Price (from Products table)
Avg Market Price =
AVERAGEX(
VALUES(Products[ProductID]),
RELATED(Products[MarketBenchmarkPrice])
)5.3 Price vs Market Variance %
Price vs Market % =
DIVIDE(
[Avg Selling Price] - [Avg Market Price],
[Avg Market Price]
)Interpretation: Positive = we charge above market (premium positioning or overpriced). Negative = we charge below market (competitive or underpriced).
5.4 Price Band (Calculated Column on Products)
Price Band =
SWITCH(TRUE(),
Products[UnitPrice] < 500, "Economy (< ₹500)",
Products[UnitPrice] < 2000, "Mid-Range (₹500–2K)",
Products[UnitPrice] < 10000, "Premium (₹2K–10K)",
"Enterprise (> ₹10K)"
)📈 Step 6 — Trend Analysis and Moving Averages
6.1 Revenue YTD
Revenue YTD =
TOTALYTD(
[Total Revenue],
Dates[Date]
)6.2 Revenue MTD
Revenue MTD =
TOTALMTD(
[Total Revenue],
Dates[Date]
)6.3 3-Month Moving Average
3M Moving Avg =
AVERAGEX(
DATESINPERIOD(
Dates[Date],
LASTDATE(Dates[Date]),
-3,
MONTH
),
[Total Revenue]
)How it works: DATESINPERIOD returns the last 3 months of dates ending at the current date in context. AVERAGEX then iterates those months and averages the revenue — producing a rolling average that smooths out monthly volatility.
6.4 12-Month Moving Average
12M Moving Avg =
AVERAGEX(
DATESINPERIOD(
Dates[Date],
LASTDATE(Dates[Date]),
-12,
MONTH
),
[Total Revenue]
)6.5 Revenue Trend Direction
Trend Direction =
VAR Current3M = [3M Moving Avg]
VAR Prior3M =
CALCULATE(
[3M Moving Avg],
DATEADD(Dates[Date], -3, MONTH)
)
RETURN
IF(
ISBLANK(Prior3M), "—",
IF(Current3M > Prior3M, "▲ Growing", "▼ Declining")
)🚨 Step 7 — Anomaly Detection
7.1 Monthly Revenue Average (for baseline)
Monthly Revenue Avg =
AVERAGEX(
ALL(Dates[Year], Dates[Month]),
[Total Revenue]
)7.2 Standard Deviation of Monthly Revenue
Revenue Std Dev =
VAR Avg = [Monthly Revenue Avg]
RETURN
SQRT(
AVERAGEX(
ALL(Dates[Year], Dates[Month]),
POWER([Total Revenue] - Avg, 2)
)
)7.3 Anomaly Z-Score
Revenue Z-Score =
DIVIDE(
[Total Revenue] - [Monthly Revenue Avg],
[Revenue Std Dev]
)Interpretation: Z-Score > 2 or < -2 indicates a statistical anomaly (more than 2 standard deviations from the mean).
7.4 Anomaly Flag
Anomaly Flag =
SWITCH(TRUE(),
[Revenue Z-Score] > 2, "⚠️ Spike",
[Revenue Z-Score] < -2, "⚠️ Drop",
"✅ Normal"
)7.5 Built-in Anomaly Detection (No DAX Required)
On your monthly revenue line chart:
- Click the chart → open Analytics pane (magnifying glass icon)
- Expand Find anomalies → toggle On
- Set Sensitivity to 80
- Anomaly dots appear on the line — hover each to see the expected range vs actual
- Click an anomaly dot → select Explain anomaly → Power BI runs a decomposition to identify which dimension (region, category, product) contributed most to the deviation
🎯 Step 8 — Dynamic Segmentation with What-If Parameter
8.1 Create the What-If Parameter
- Modeling tab → New Parameter
- Name:
Top N Products - Data type: Whole Number
- Minimum: 1, Maximum: 50, Increment: 1, Default: 10
- Tick Add slicer to this page
Power BI auto-creates a table called Top N Products with a column Top N Products Value and a measure Top N Products.
8.2 Dynamic Top N Measure
Is In Top N =
IF(
[Product Revenue Rank] <= [Top N Products],
"In Top N",
"Others"
)8.3 Performance Band — Dynamic Threshold
Performance Band =
VAR CurrentRevenue = [Total Revenue]
VAR AllRevenue = CALCULATE([Total Revenue], ALL(Products))
VAR Pct = DIVIDE(CurrentRevenue, AllRevenue)
RETURN
SWITCH(TRUE(),
Pct >= 0.05, "High Contributor (≥5%)",
Pct >= 0.01, "Mid Contributor (1–5%)",
"Low Contributor (<1%)"
)📊 Step 9 — Final Dashboard: Put It All Together
Build a 3-page report using all the measures you created:
Page 1 — Executive Overview
- KPI Cards: Total Revenue, YoY Growth %, Gross Margin %, Total Orders
- Line chart: Monthly Revenue + 3M Moving Avg + Forecast (3 months) with Anomaly Detection enabled
- Matrix: Region × Year with Total Revenue and YoY Growth % + conditional formatting
- Slicer: Year
Page 2 — Product & Pricing Intelligence
- Table: ProductName, Category, Total Revenue, Product Revenue Rank, Category Rank, Price Band, Price vs Market %
- Clustered bar: Category vs Avg Selling Price vs Avg Market Price
- What-If slicer for Top N + filtered bar chart
- Slicer: Category, Price Band
Page 3 — Customer Analytics
- Donut: Customer Segment distribution
- Bar: Revenue by Customer Segment
- Table: CustomerName, Region, RFM Score, Customer Segment, Total Revenue, Customer Revenue Rank
- Card: Count of Champions, Count of At Risk customers
- Slicer: Region, Customer Segment
🧠 Knowledge Quiz — Test Your Understanding
10 questions. No peeking at the DAX above. Write your answer before revealing.
-
Q1 — Why use DIVIDE() instead of the / operator in DAX?
Reveal Answer
DIVIDE(a, b) returns BLANK (or a custom alternate result) when b is zero, avoiding a division-by-zero error. The / operator throws an error. In filtered visuals where no data exists, DIVIDE keeps the report clean — the cell shows blank instead of an error symbol.
-
Q2 — What is the difference between ALL() and ALLEXCEPT() in RANKX?
Reveal Answer
ALL(Products[ProductName]) ranks across every product ignoring all filters — giving a global rank. ALLEXCEPT(Products, Products[Category]) removes all filters EXCEPT Category — so the rank resets within each category, giving a category-level rank.
-
Q3 — Why is RFM scoring implemented as calculated columns rather than measures?
Reveal Answer
Each customer has a fixed score based on their full purchase history — it does not change when a visual filter is applied. Calculated columns evaluate at data refresh time and store the result per row, making them appropriate for stable per-entity attributes like RFM scores. Measures re-evaluate dynamically on every visual interaction.
-
Q4 — What does SAMEPERIODLASTYEAR() require to work correctly?
Reveal Answer
A Date table that is marked as a Date Table (Table Tools → Mark as date table) and a continuous date range with no gaps. Without a marked Date table, Power BI cannot correctly interpret time intelligence functions like SAMEPERIODLASTYEAR, TOTALYTD, or DATEADD.
-
Q5 — What does a Z-Score of -2.5 mean for a monthly revenue value?
Reveal Answer
The month's revenue is 2.5 standard deviations below the historical average — a statistically significant downward anomaly. Values beyond ±2 standard deviations typically represent unusual events worth investigating (promotions, supply issues, external shocks).
-
Q6 — Why does the 3-Month Moving Average use AVERAGEX with DATESINPERIOD rather than a simple AVERAGE?
Reveal Answer
AVERAGE(Sales[Revenue]) would average individual transaction amounts — not monthly totals. AVERAGEX iterates over each month in the 3-month window and evaluates [Total Revenue] per month, then averages those monthly totals. DATESINPERIOD generates the correct rolling 3-month date range relative to the current context date.
-
Q7 — In the Price vs Market % measure, what does a result of +0.15 (15%) mean and what business decision might it trigger?
Reveal Answer
The average selling price is 15% above the market benchmark. This could indicate strong brand premium (positive) or potential overpricing causing volume loss (negative). The business decision depends on whether units sold are growing or shrinking — combine with YoY Units to distinguish premium positioning from demand-killing overpricing.
-
Q8 — What is the key difference between TOTALYTD() and using CALCULATE() with DATESYTD()?
Reveal Answer
They produce the same result. TOTALYTD([Total Revenue], Dates[Date]) is syntactic sugar for CALCULATE([Total Revenue], DATESYTD(Dates[Date])). TOTALYTD is simpler to write; CALCULATE + DATESYTD gives more flexibility — for example, you can add additional filter arguments to CALCULATE that TOTALYTD does not support.
-
Q9 — A product has a Category Rank of 1 but a Global Product Revenue Rank of 47. What does this tell you?
Reveal Answer
The product is the top seller within its own category, but that category generates relatively modest revenue compared to others. The category itself is a smaller contributor globally. This insight guides where to invest — the product is a category leader but the category may need development, or the business may choose to deprioritise it in favour of higher-revenue categories.
-
Q10 — Why does built-in Power BI Anomaly Detection require a continuous date axis and not work with a categorical month name column?
Reveal Answer
The SR-CNN algorithm that powers Power BI anomaly detection requires temporal ordering — it models expected values based on time sequence and seasonality patterns. A categorical text axis (January, February…) does not communicate temporal order or spacing to the algorithm. Only a proper date/time column on the axis enables Power BI to interpret the sequence correctly and calculate expected value ranges.
Quick Knowledge Check
Q1. You write Sales MTD = TOTALMTD([Sales], Dates[Date]). On June 15, this measure shows the sum of sales from June 1 to June 15. What happens to this measure's value on June 30?
Show Answer
It shows the cumulative total from June 1 to June 30 — month-to-date resets at the start of each month. TOTALMTD uses DATESYTD internally but scoped to the current month. On July 1, it resets to just July 1's sales. Use this for running totals within a month (e.g., monthly sales progress dashboards). It requires a properly marked Date table with continuous dates and no gaps.
Q2. A colleague writes Revenue LY = CALCULATE([Revenue], SAMEPERIODLASTYEAR(Dates[Date])). The measure works in a bar chart but shows BLANK in a card visual filtered to a single date. Why?
- A) SAMEPERIODLASTYEAR only works with month-level granularity, not individual dates
- B) The card is filtered to a single date; SAMEPERIODLASTYEAR returns the prior-year date, but if that date has no transaction data, the measure returns BLANK
- C) CALCULATE cannot be used inside SAMEPERIODLASTYEAR
- D) The Date table must be disconnected for SAMEPERIODLASTYEAR to work
Show Answer
B. SAMEPERIODLASTYEAR shifts the filter context to the same period one year earlier. If June 15 last year had no transactions (weekend, holiday, or simply no data), [Revenue] returns BLANK for that single date. In a bar chart aggregated by month, this is averaged out. The fix is to use a different grain for the comparison or handle BLANK with IF(ISBLANK(...), 0, ...) or COALESCE.
Q3. What is a "disconnected table" in Power BI and how is it used for what-if comparative analytics?
Show Answer
A disconnected table has no relationship to other model tables — it is used as a slicer input to a SELECTEDVALUE() or VALUES() DAX expression. For example, a table with metric names ("Revenue", "Units", "Margin") has no relationship to the Sales table. A measure uses SWITCH(SELECTEDVALUE(MetricTable[Metric]), "Revenue", [Revenue], "Units", [Units], ...) to return the right measure based on slicer selection. This creates a dynamic metric selector — the user chooses which KPI the chart displays, all from one visual. It is a powerful pattern for space-efficient comparative dashboards.
- TOTALMTD resets at month start — gives cumulative month-to-date. Requires a continuous Date table with no gaps.
- SAMEPERIODLASTYEAR shifts context by 365 days — BLANK if the prior-year period has no data. Handle with COALESCE or an IF/ISBLANK wrapper.
- RANKX in a measure ranks dynamically by filter context — rank changes when slicers filter. Use a calculated column for a fixed global rank that doesn't change.
- Disconnected tables power dynamic metric selectors — no relationship needed. Use SELECTEDVALUE() + SWITCH() to let users pick which KPI to display in a single visual.
- Always test time intelligence with a proper Date table — Mark the table as Date table in Desktop. Time intelligence functions (TOTALYTD, SAMEPERIODLASTYEAR, etc.) require a complete, gap-free calendar column.