Power BI Sales Dashboard — Complete Project with DAX

We're building a comprehensive sales dashboard for a fictional e-commerce company (inspired by Flipkart's analytics needs).

Dashboard Features

KPI Cards:

• Total Revenue (with YoY growth %)
• Total Orders (with MoM comparison)
• Average Order Value
• Profit Margin %

Visuals:

1. Revenue trend line (daily/monthly with drill-down)
2. Revenue by Product Category (bar chart)
3. Revenue by Region (map)
4. Top 10 Products by Revenue (table)
5. Running Total Revenue (cumulative line chart)
6. Revenue vs Last Year comparison (line chart with two series)

Interactivity:

• Date slicer (filter entire dashboard)
• Category filter (multi-select)
• Region filter (dropdown)
• Cross-filtering between all visuals

Skills You'll Learn

Data Modeling:

• Create a Date dimension table (required for time intelligence)
• Set up relationships (Sales ↔ Date, Sales ↔ Products)
• Configure relationship cardinality and filter direction

DAX Measures:

• Basic aggregations (SUM, COUNT, AVERAGE)
• Time intelligence (YTD, MTD, YoY, MoM)
• Running totals (CALCULATE with date filters)
• Conditional calculations (SWITCH, IF statements)
• Advanced filters (CALCULATE, FILTER, ALL)

Dashboard Design:

• Layout best practices
• Color schemes and formatting
• Interactive features (slicers, drill-down)

Dataset

Source: Download this sample sales dataset: Flipkart-style Sales Data (CSV)

Columns:

• Order_ID — Unique order identifier
• Order_Date — Date of order
• Product_Name — Product name
• Category — Product category (Electronics, Fashion, Home, etc.)
• Region — Sales region (North, South, East, West)
• Quantity — Units sold
• Revenue — Total revenue (₹)
• Cost — Cost of goods sold (₹)
• Customer_ID — Customer identifier

Size: ~5,000 orders (Jan 2024 - Dec 2025)

Load the CSV File

1. Open Power BI Desktop
2. Home → Get Data → Text/CSV
3. Select sales_data.csv → Open
4. Preview window appears → Click Transform Data

Clean Data in Power Query

// Step 1: Remove unnecessary columns (if any)
// Right-click "Internal_ID" (if exists) → Remove

// Step 2: Set correct data types
Order_Date: Date
Revenue: Currency (Decimal Number)
Cost: Currency (Decimal Number)
Quantity: Whole Number
Order_ID: Text
Customer_ID: Text

// Step 3: Add calculated columns
// Add Column → Custom Column → Name: Profit
[Revenue] - [Cost]

// Add Column → Custom Column → Name: Profit_Margin
([Revenue] - [Cost]) / [Revenue]

// Step 4: Filter out returns/cancelled orders (if Status column exists)
// Filter "Order_Status" to show only "Delivered"

// Step 5: Verify no duplicates
// Home → Keep Rows → Remove Duplicates (based on Order_ID)

Close and Apply

Click Close & Apply (top-left ribbon) to load transformed data into Power BI.

Your Sales table now appears in Fields pane with these columns:

• Order_ID, Order_Date, Product_Name, Category, Region, Quantity, Revenue, Cost, Profit, Profit_Margin, Customer_ID

Time intelligence in Power BI REQUIRES a separate Date table (not just dates in Sales table).

Why You Need a Date Table

Problem without Date table:

• YTD, MTD, YoY calculations don't work
• Can't create fiscal calendars (if year starts in April, not January)
• Missing dates (days with no sales) break trend lines

Solution: Create a continuous Date table with ALL dates (2024-01-01 to 2025-12-31).

Create Date Table with DAX

Option 1: Quick Date Table (Auto-Generated)

1. Modeling tab → New Table
2. Paste this DAX:

Date =
ADDCOLUMNS(
    CALENDAR(DATE(2024, 1, 1), DATE(2025, 12, 31)),
    "Year", YEAR([Date]),
    "Month Number", MONTH([Date]),
    "Month Name", FORMAT([Date], "MMM"),
    "Month Year", FORMAT([Date], "MMM YYYY"),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "Day of Week", FORMAT([Date], "dddd"),
    "Day Number", DAY([Date]),
    "Week Number", WEEKNUM([Date]),
    "Is Weekend", IF(WEEKDAY([Date], 2) >= 6, "Yes", "No")
)

What this does:

• CALENDAR(start, end) creates a table with all dates from Jan 1, 2024 to Dec 31, 2025
• ADDCOLUMNS adds calculated columns for Year, Month, Quarter, etc.
• FORMAT converts dates to text (e.g., "Jan 2024", "Monday")

Option 2: Full Date Table (Production-Ready)

For real projects, add more columns (Fiscal Year, Holidays, etc.):

Date =
VAR StartDate = DATE(2024, 1, 1)
VAR EndDate = DATE(2025, 12, 31)
RETURN
ADDCOLUMNS(
    CALENDAR(StartDate, EndDate),
    "Year", YEAR([Date]),
    "Year Month", FORMAT([Date], "YYYY-MM"),
    "Month Number", MONTH([Date]),
    "Month Name", FORMAT([Date], "MMMM"),
    "Month Short", FORMAT([Date], "MMM"),
    "Month Year", FORMAT([Date], "MMM YYYY"),
    "Quarter", "Q" & QUARTER([Date]),
    "Quarter Year", "Q" & QUARTER([Date]) & " " & YEAR([Date]),
    "Day of Week", FORMAT([Date], "dddd"),
    "Day of Week Number", WEEKDAY([Date], 2),  // 1=Monday, 7=Sunday
    "Day of Month", DAY([Date]),
    "Day of Year", DATEDIFF(DATE(YEAR([Date]), 1, 1), [Date], DAY) + 1,
    "Week of Year", WEEKNUM([Date], 2),
    "Is Weekend", IF(WEEKDAY([Date], 2) >= 6, TRUE, FALSE),
    "Is Weekday", IF(WEEKDAY([Date], 2) < 6, TRUE, FALSE),
    "Fiscal Year", IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1),  // FY starts April 1
    "Fiscal Quarter", "FY Q" & CEILING(IF(MONTH([Date]) >= 4, MONTH([Date]) - 3, MONTH([Date]) + 9), 3) / 3
)

Press Enter → Date table created with 730 rows (2 years × 365 days)

Mark as Date Table

Critical step (tells Power BI this is the official Date table):

1. Right-click Date table in Fields pane → Mark as Date Table
2. Select Date column as the date column → OK

Create Relationship

Connect Sales table to Date table:

1. Home → Manage Relationships → New
2. From: Sales[Order_Date]
3. To: Date[Date]
4. Cardinality: Many to One (*:1) — Many sales on one date
5. Cross Filter Direction: Single (Date filters Sales)
6. Make this relationship active: Checked
7. Click OK

Verify: Relationship appears as a line between Sales and Date tables in Model view.

Measures are where Power BI shines. Let's build 15 essential measures for the dashboard.

Basic Aggregation Measures

1. Total Revenue

Total Revenue = SUM(Sales[Revenue])

2. Total Orders

Total Orders = COUNTROWS(Sales)
// OR: Total Orders = DISTINCTCOUNT(Sales[Order_ID])  // if duplicates possible

3. Total Profit

Total Profit = SUM(Sales[Profit])

4. Average Order Value (AOV)

Avg Order Value =
DIVIDE(
    [Total Revenue],
    [Total Orders],
    0  // Returns 0 if denominator is 0 (avoids divide-by-zero error)
)

5. Profit Margin %

Profit Margin % =
DIVIDE(
    [Total Profit],
    [Total Revenue],
    0
)
// Format this measure as Percentage (0-1 → 0%-100%)

Time Intelligence Measures

6. Year-to-Date (YTD) Revenue

Revenue YTD =
TOTALYTD(
    [Total Revenue],
    Date[Date]
)
// Requires Date table marked as Date table
// Returns revenue from Jan 1 to current date (filtered date context)

7. Month-to-Date (MTD) Revenue

Revenue MTD =
TOTALMTD(
    [Total Revenue],
    Date[Date]
)
// Revenue from start of month to current date

8. Previous Year Revenue (for YoY comparison)

Revenue Last Year =
CALCULATE(
    [Total Revenue],
    SAMEPERIODLASTYEAR(Date[Date])
)
// Returns revenue for same date range, but last year
// Example: If viewing Jan-Mar 2025, returns Jan-Mar 2024 revenue

9. Year-over-Year (YoY) Growth %

Revenue YoY Growth % =
VAR CurrentRevenue = [Total Revenue]
VAR LastYearRevenue = [Revenue Last Year]
VAR Growth = CurrentRevenue - LastYearRevenue
RETURN
    DIVIDE(Growth, LastYearRevenue, 0)
// Format as Percentage
// Example: 0.25 = 25% growth

10. Month-over-Month (MoM) Growth %

Revenue MoM Growth % =
VAR CurrentMonthRevenue = [Total Revenue]
VAR PreviousMonthRevenue =
    CALCULATE(
        [Total Revenue],
        DATEADD(Date[Date], -1, MONTH)
    )
VAR Growth = CurrentMonthRevenue - PreviousMonthRevenue
RETURN
    DIVIDE(Growth, PreviousMonthRevenue, 0)

Running Total and Cumulative Measures

11. Running Total Revenue

Running Total Revenue =
CALCULATE(
    [Total Revenue],
    FILTER(
        ALL(Date[Date]),
        Date[Date] <= MAX(Date[Date])
    )
)
// Sums revenue from start of time to current date (ignores date filters partially)
// ALL removes date filters, then FILTER reapplies up to current date

Alternative (simpler, but only for YTD running total):

Running Total Revenue YTD =
TOTALYTD([Total Revenue], Date[Date])

Conditional and Advanced Measures

12. Revenue by Category (Dynamic)

Revenue - Selected Category =
CALCULATE(
    [Total Revenue],
    ALLSELECTED(Sales[Category])
)
// Respects slicers/filters EXCEPT Category filter (useful for "% of total" calcs)

13. Top Products Revenue (Top 10)

Top 10 Products Revenue =
CALCULATE(
    [Total Revenue],
    TOPN(
        10,
        ALL(Sales[Product_Name]),
        [Total Revenue],
        DESC
    )
)
// Returns revenue from top 10 products only

14. Revenue Rank (by Product)

Product Revenue Rank =
RANKX(
    ALL(Sales[Product_Name]),
    [Total Revenue],
    ,
    DESC,
    DENSE
)
// Ranks products by revenue (1 = highest revenue)
// Use in table visual to show product rankings

15. Dynamic Title (shows selected date range)

Dashboard Title =
VAR MinDate = MIN(Date[Date])
VAR MaxDate = MAX(Date[Date])
RETURN
    "Sales Dashboard: " & FORMAT(MinDate, "DD MMM YYYY") & " to " & FORMAT(MaxDate, "DD MMM YYYY")
// Use this measure in a Card visual as dashboard title
// Example output: "Sales Dashboard: 01 Jan 2025 to 31 Mar 2025"

Format Measures

After creating each measure:

1. Click measure in Fields pane
2. Measure Tools ribbon → Format dropdown:
   • Revenue/Profit → Currency (₹), 2 decimals
   • Percentages → Percentage, 1 decimal
   • Orders → Whole Number, 0 decimals
3. Display folder (organize measures):
   • Right-click measure → Display Folder → "Revenue Metrics" or "Time Intelligence"

Now let's assemble visuals into a polished dashboard.

Layout Structure (Recommended)

Visual 1: KPI Cards (Row 1)

Total Revenue Card:

1. Visualizations → Card
2. Drag Total Revenue measure to Fields well
3. Format:
   • Callout value: Font size 32, bold
   • Category label: "Total Revenue"
   • Background: Light gray (#F5F5F5)

Add YoY Growth indicator:

1. Visualizations → Multi-row Card (better than Card)
2. Drag Total Revenue and Revenue YoY Growth % to Fields
3. Conditional formatting on YoY Growth:
   • Format → Callout value → Conditional formatting
   • If > 0 → Green, If < 0 → Red

Repeat for: Total Orders (with MoM growth), Avg Order Value, Profit Margin %

Visual 2: Revenue Trend Line Chart

1. Visualizations → Line Chart
2. X-axis: Date[Date] (or Date Hierarchy for drill-down)
3. Y-axis: Total Revenue
4. Legend (optional): Category (shows multiple lines per category)
5. Format:
   • Title: "Revenue Trend"
   • X-axis: Label "Date"
   • Y-axis: Label "Revenue (₹)", format as Currency
   • Add Average line: Analytics pane → Average line → Add
   • Gridlines: Subtle (light gray)
6. Enable drill-down:
   • Use Date Hierarchy (Year → Quarter → Month → Day)
   • Click drill-down arrow to go from yearly → monthly view

Visual 3: Revenue by Category (Bar Chart)

1. Visualizations → Stacked Bar Chart (horizontal)
2. Y-axis: Sales[Category]
3. X-axis: Total Revenue
4. Sort: Click More options (…) → Sort by → Revenue → Descending
5. Data labels: On (show revenue values on bars)
6. Format:
   • Title: "Revenue by Category"
   • Colors: Use consistent brand colors (blue shades)

Visual 4: Revenue by Region (Map)

1. Visualizations → Map (or Filled Map)
2. Location: Sales[Region]
3. Size: Total Revenue (bubble size by revenue)
4. Format:
   • Title: "Revenue by Region"
   • Map style: Light (better for presentations)

Note: For Indian state-level maps, you'll need to geocode regions or use a custom map visual (Mapbox, etc.)

Visual 5: Running Total Revenue (Area Chart)

1. Visualizations → Area Chart
2. X-axis: Date[Date]
3. Y-axis: Running Total Revenue measure
4. Format:
   • Title: "Cumulative Revenue"
   • Fill color: Light blue with transparency
   • Line color: Dark blue

Visual 6: This Year vs Last Year Comparison

1. Visualizations → Line Chart
2. X-axis: Date[Month Year] (e.g., "Jan 2025")
3. Y-axis: Create two measures:
   • Total Revenue (this year)
   • Revenue Last Year (previous year)
4. Legend: Auto-populated (two series)
5. Format:
   • Title: "Revenue: This Year vs Last Year"
   • Line colors: Blue (current), Gray (last year)
   • Data labels: Off (clutters chart)

Visual 7: Top 10 Products Table

1. Visualizations → Table
2. Columns: Drag in order:
   • Sales[Product_Name]
   • Product Revenue Rank (measure)
   • Total Revenue
   • Total Profit
   • Profit Margin %
3. Filter: Filters pane → Add filter → Product Revenue Rank → Show items ≤ 10
4. Format:
   • Conditional formatting on Revenue (data bars)
   • Conditional formatting on Profit Margin (color scale: red < 10%, green > 20%)
   • Alternate row shading (easier to read)

Add Slicers (Filters)

Date Range Slicer:

1. Visualizations → Slicer
2. Field: Date[Date]
3. Format → Slicer settings → Style: Between (range slider)
4. Place at top of dashboard

Category Multi-Select Slicer:

1. Visualizations → Slicer
2. Field: Sales[Category]
3. Format → Selection controls → Multi-select: On
4. Place on left sidebar

Region Dropdown:

1. Visualizations → Slicer
2. Field: Sales[Region]
3. Format → Slicer settings → Style: Dropdown
4. Place on right sidebar

Final Touches

Dashboard Title:

• Add a Card visual at the top
• Use Dashboard Title measure (dynamic date range)
• Format: Large font (28pt), bold, centered

Background and Theming:

• View → Themes → Select a theme (e.g., "Executive")
• Or: Format canvas → Canvas background → Light gray (#F8F8F8)

Align and Distribute:

• Select multiple visuals → Format → Align → Distribute horizontally
• Snap to grid (View → Page view → Show gridlines)

Test Cross-Filtering

1. Click a bar in "Revenue by Category" (e.g., Electronics)
2. Verify: All other visuals filter to Electronics only:
   • KPI cards show Electronics revenue/orders
   • Trend line shows Electronics revenue over time
   • Map shows Electronics revenue by region
   • Table shows top Electronics products
3. Click bar again to deselect (return to all data)

Test Slicers

Date Slicer:

• Drag slider to select Q1 2025 (Jan-Mar)
• Verify all visuals update to Q1 data
• Check YoY growth (should compare Q1 2025 vs Q1 2024)

Category Slicer:

• Select "Electronics" and "Fashion" (multi-select)
• Verify dashboard shows only these two categories
• Verify KPI cards aggregate both categories

Test Drill-Down

In Revenue Trend Chart:

1. Click drill-down arrow (top-right of visual)
2. Click a year (e.g., 2025)
3. Chart expands to show quarters within 2025
4. Click a quarter → Expands to months
5. Click drill-up arrow to go back

Edit Interactions (Optional)

Sometimes you don't want cross-filtering:

Example: KPI cards shouldn't change when clicking trend line

1. Click Revenue Trend visual
2. Format tab → Edit Interactions
3. Other visuals show filter/highlight icons
4. Click None icon on KPI cards (they won't filter when trend is clicked)
5. Click Edit Interactions again to exit mode