Revenue Leakage Dashboard with SQL

Build a Power BI dashboard backed by SQL queries to track revenue leakage.

Introduction

30 Second Summary

Every business loses money quietly through refunds, chargebacks, and failed payments. The real challenge is not finding the leaks. It is showing decision-makers exactly where money is going so they can fix it.

In this project, you will build an interactive Revenue Leakage Dashboard using SQL Server and Power BI. You will write diagnostic SQL queries to quantify where money is leaking, then visualize the results in a polished dashboard that executives can actually use.

What You'll Build

An interactive Power BI dashboard connected to a SQL Server database, showing where revenue is being lost across categories, regions, and time periods.

By the end of this project, you'll have:

  • A SQL Server database with a normalized transactions schema loaded with sample data from CSV.
  • A set of diagnostic SQL queries using CTEs, CASE expressions, and window functions to categorize and quantify revenue leakage.
  • A polished Power BI dashboard with KPI cards, a category breakdown chart, a time-series trend line, and regional comparisons powered by DAX measures.
  • Secret Mission: A drill-through page that lets stakeholders click on a leakage category to see individual flagged transactions.

Are there any prerequisites?

This project builds on the concepts from the "Diagnose Revenue Leakage" Python project. You should be familiar with revenue leakage categories like refunds, chargebacks, and discount abuse.

You will also need a Windows machine, since both Power BI Desktop and SQL Server Express LocalDB are Windows-only tools.

Set Up Your SQL Environment

Before you can write SQL queries or build dashboards, you need a database engine running on your machine.

In this step, you will install SQL Server Express LocalDB as your lightweight database engine, add the mssql extension to VS Code, and connect everything together so you can write and run SQL directly from your editor.

In this step, get ready to:
  • Install SQL Server Express LocalDB and verify it is running.
  • Install the mssql extension in VS Code.
  • Connect VS Code to your LocalDB instance.
Install SQL Server Express LocalDB

LocalDB is a lightweight version of SQL Server designed for developers. It starts automatically when you connect and requires no background services running all the time.

What is LocalDB?

LocalDB gives you a full SQL Server engine without the complexity of a server installation. It auto-starts when your application connects and shuts down when idle.

Power BI can connect to it natively using the built-in SQL Server connector, making it a perfect fit for this project.

  • Locate the SqlLocalDB.msi installer you downloaded earlier.
  • Double-click the installer to launch it.
  • Accept the license agreement and click Install.
  • Wait for the installation to complete, then click Finish.

Where do I get the installer?

The SqlLocalDB.msi installer is available from the SQL Server Express download page on Microsoft's website. The exact download URL changes over time, so search for "SQL Server Express LocalDB download" if you need to find it again.

Now verify that LocalDB installed correctly and that the default instance is available.

  • Press the Windows key to open your search bar.
  • Type cmd and press Enter to open a command prompt.
  • Verify your LocalDB installation by running this command:
sqllocaldb info

You should see MSSQLLocalDB listed in the output. This is the default automatic instance that LocalDB creates during installation.

Don't see MSSQLLocalDB?

If you see "'sqllocaldb' is not recognized", the installer may not have completed successfully. Try running the SqlLocalDB.msi installer again.

If the command works but the list is empty, create the default instance manually by running sqllocaldb create MSSQLLocalDB in your command prompt.

Help me troubleshoot my LocalDB installation.

Install the mssql extension in VS Code

The mssql extension turns VS Code into a full SQL editor with IntelliSense, query execution, and a database object explorer.

  • Press the Windows key to open your search bar.
  • Type VS Code and press Enter to open it.
  • Press Ctrl+Shift+X to open the Extensions view.
  • Type mssql in the search box at the top.
  • Find the extension called SQL Server (mssql) by Microsoft.
  • Click Install.

After installation completes, you should see a new SQL Server icon appear in the Activity Bar on the left side of VS Code.

Don't see the SQL Server icon?

Make sure you installed the extension by Microsoft (publisher ID ms-mssql.mssql). Other similarly named extensions exist but may not include the connection features.

Try reloading VS Code by pressing Ctrl+Shift+P and typing Reload Window.

Help me find the mssql extension in VS Code.

Connect VS Code to your LocalDB instance

With LocalDB installed and the extension ready, you can now connect VS Code directly to your database engine. This connection lets you write and execute SQL queries without leaving your editor.

  • Press F1 to open the Command Palette.
  • Type MS SQL: Add Connection and select it from the results.
  • In the Server name field, enter (localdb)\MSSQLLocalDB.
  • For Authentication Type, select Windows Authentication.
  • Leave the Database name field empty and press Enter.
  • When prompted for a profile name, enter RevenueLeakage and press Enter.

Why Windows Authentication?

LocalDB runs under your Windows user account, so no separate username or password is needed. Windows Authentication uses your current login session to verify access automatically.

  • Click the SQL Server icon in the Activity Bar on the left side of VS Code.
  • Expand the Connections panel.

You should see your (localdb)\MSSQLLocalDB server listed with a green dot indicating an active connection.

Connection failed?

If the connection times out, LocalDB may not have started yet. Open a command prompt and run sqllocaldb start MSSQLLocalDB to manually start the instance, then try connecting again.

Double-check that you typed the server name exactly as (localdb)\MSSQLLocalDB with the backslash between (localdb) and MSSQLLocalDB.

Help me fix my LocalDB connection in VS Code.

Your SQL environment is ready to go. Next up, you will create the database and schema that will hold your revenue transaction data.

Create the Revenue Database and Schema

Your SQL environment is connected and ready. Now it's time to create the database that will store all your revenue transaction data.

Every analytics project needs a well-designed schema to organize its data. You'll create a dedicated database with two tables: one for raw transaction records, and one for calendar dates that power time-based analysis.

In this step, get ready to:
  • Create the RevenueLeakage database on your LocalDB instance.
  • Build the transactions table with columns for amounts, types, and regions.
  • Build a calendar table for time-based reporting in SQL and Power BI.
Create a SQL file and the database

You'll keep your SQL scripts in a project folder so they stay organized as you add more files in later steps.

  • Create a new folder on your Desktop called revenue-dashboard.
  • In VS Code, click File in the top menu bar.
  • Click Open Folder and select the revenue-dashboard folder you just created on your Desktop.
  • Create a new file by pressing Ctrl+N.
  • Save the file immediately with Ctrl+S and name it 01_create_database.sql.

The first thing your script needs to do is create the database itself. Databases in SQL Server are containers that hold all your tables, views, and other objects.

  • Add the following code to your 01_create_database.sql file:
-- Create the RevenueLeakage database
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'RevenueLeakage')
    CREATE DATABASE RevenueLeakage;
GO

USE RevenueLeakage;
GO

What does this code do?

  • IF NOT EXISTS checks whether a database called RevenueLeakage already exists. This prevents errors if you run the script more than once.
  • CREATE DATABASE creates the new database on your LocalDB instance.
  • USE RevenueLeakage switches context so that all following commands target the new database instead of the default master database.
  • GO is a batch separator in T-SQL. It tells SQL Server to execute everything above it before moving on.
  • Save the file with Ctrl+S. You'll execute the full script after adding the remaining tables.
Define the transactions table

The transactions table is the core of your revenue analysis. Each row represents one financial transaction, with columns that capture the amount, type, and context you need to diagnose leakage.

  • Add the following code below your existing GO statement in 01_create_database.sql:
-- Create the transactions table
CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    transaction_date DATE NOT NULL,
    gross_amount DECIMAL(10, 2) NOT NULL,
    net_amount DECIMAL(10, 2) NOT NULL,
    transaction_type VARCHAR(20) NOT NULL,
    customer_id INT NOT NULL,
    product_category VARCHAR(50) NOT NULL,
    region VARCHAR(30) NOT NULL
);
GO

What does this table store?

  • transaction_id is the primary key. Every row has a unique identifier.
  • gross_amount and net_amount let you compare what was billed versus what was actually received.
  • transaction_type classifies each transaction as valid_sale, refund, chargeback, discount_abuse, or failed_payment. This is the column your leakage queries will filter on.
  • region and product_category give you dimensions to slice the data by geography and product line.
  • Save the file with Ctrl+S. One more table to add before you execute.
Define the calendar table and execute the script

A calendar table (also called a date dimension) gives you pre-built columns for month, quarter, and year. This makes time-based grouping much easier in both SQL queries and Power BI visuals.

  • Add the following code below the transactions table's GO statement:
-- Create the calendar table
CREATE TABLE calendar (
    date_key DATE PRIMARY KEY,
    month_number INT NOT NULL,
    month_name VARCHAR(20) NOT NULL,
    quarter_number INT NOT NULL,
    year_number INT NOT NULL
);
GO

Why a separate calendar table?

You could extract month and year directly from transaction_date every time you query. But a dedicated calendar table is the industry-standard approach for BI projects.

It gives Power BI a clean date axis for charts, and it lets you add custom time periods (fiscal quarters, holidays) without changing your transaction data.

Your full script is ready. Time to execute it and create everything on the server.

  • Make sure your cursor is inside the 01_create_database.sql file.
  • Press Ctrl+Shift+E to execute the entire script.
  • If prompted to select a connection, choose the (localdb)\MSSQLLocalDB connection you set up in Step 1.
  • Check the Results panel at the bottom of VS Code. You should see messages confirming each command completed successfully (no red error text).
  • In the SQL Server panel on the left sidebar, expand your connection, then expand Databases. You should see RevenueLeakage listed.
  • Expand RevenueLeakage, then Tables. You should see both dbo.transactions and dbo.calendar.

Seeing a red error message?

  • If you see "There is already an object named 'transactions'", it means the script was run before. Delete the database first by running DROP DATABASE RevenueLeakage; and then re-execute your script.
  • If you see "Login failed" or "Cannot open database", make sure you selected the (localdb)\MSSQLLocalDB connection when prompted. Check the bottom status bar in VS Code to see which server is active.
  • If the tables don't appear in the sidebar, right-click your connection in the SQL Server panel and select Refresh.

Why is my SQL script failing to execute?

✔️ Awesome, I've got everything!

Great. Double check you've saved your file before moving on.

ⓧ I'd like to double check the full code

Here is the complete 01_create_database.sql file. Make sure yours matches exactly:

-- Create the RevenueLeakage database
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'RevenueLeakage')
    CREATE DATABASE RevenueLeakage;
GO

USE RevenueLeakage;
GO

-- Create the transactions table
CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    transaction_date DATE NOT NULL,
    gross_amount DECIMAL(10, 2) NOT NULL,
    net_amount DECIMAL(10, 2) NOT NULL,
    transaction_type VARCHAR(20) NOT NULL,
    customer_id INT NOT NULL,
    product_category VARCHAR(50) NOT NULL,
    region VARCHAR(30) NOT NULL
);
GO

-- Create the calendar table
CREATE TABLE calendar (
    date_key DATE PRIMARY KEY,
    month_number INT NOT NULL,
    month_name VARCHAR(20) NOT NULL,
    quarter_number INT NOT NULL,
    year_number INT NOT NULL
);
GO

Your database structure is in place. Next up, you'll load thousands of transaction records into these tables so you have real data to analyze.

Load the Sample Data

Your database has a clean structure with two empty tables waiting for data. Now it is time to fill them.

In this step, you will load 5,000 transactions from a CSV file into your transactions table using BULK INSERT. Then you will populate the calendar table automatically using a recursive CTE that generates every date in the transaction range.

In this step, get ready to:
  • Download the sample CSV file and place it in the correct location.
  • Load the CSV data into your transactions table using BULK INSERT.
  • Populate the calendar table with a recursive CTE.
Download and place the sample CSV file

SQL Server's BULK INSERT command reads data directly from a file path on your machine. The file needs to be in a location that LocalDB can access.

  • Download the sample transactions.csv file from the project resources.
  • Create a folder called Revenue at the root of your C: drive.
  • Move transactions.csv into C:\Revenue\ so the full path is C:\Revenue\transactions.csv.

Why this specific path?

LocalDB runs under your Windows user account, so it can access files on your local drives. Placing the CSV at C:\Revenue\ keeps the path short and avoids spaces or special characters that can break BULK INSERT.

  • Open File Explorer and navigate to C:\Revenue\.
  • Confirm you see transactions.csv listed in the folder.

What is in this CSV?

The file contains 5,000 transactions spanning 12 months, multiple regions, and all five leakage categories: valid sales, refunds, chargebacks, discount abuse, and failed payments. Each row maps directly to the columns you created in the transactions table.

Load the CSV into the transactions table

With the file in place, you can use T-SQL's BULK INSERT command to load all 5,000 rows in one operation.

  • In VS Code, create a new file called 02_load_data.sql in the same folder as your first SQL file.
  • Add the following code to set the database context and load the CSV:
USE RevenueLeakage;
GO

-- Load transactions from CSV
BULK INSERT transactions
FROM 'C:\Revenue\transactions.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2,
    TABLOCK
);
GO

What does this code do?

  • FIELDTERMINATOR = ',' tells SQL Server that commas separate each column value in the CSV.
  • ROWTERMINATOR = '\n' tells it that each new line is a new row.
  • FIRSTROW = 2 skips the header row so only actual data gets inserted.
  • TABLOCK acquires a table-level lock for faster bulk loading.
  • Select the code you just added, then press Ctrl+Shift+E to execute it.
  • You should see a message in the results pane showing 5000 rows affected.

Seeing a file path error?

  • Double check that transactions.csv is at exactly C:\Revenue\transactions.csv. The path is case-sensitive on some configurations.
  • Make sure the file is not open in Excel or another program. An open file can block SQL Server from reading it.
  • If you see a permissions error, try running VS Code as Administrator.

Help me troubleshoot my BULK INSERT error

Populate the calendar table with a recursive CTE

A calendar table (also called a date dimension) gives you clean month, quarter, and year columns to group by in both SQL and Power BI. Instead of manually entering dates, you can generate them automatically from the transaction data you just loaded.

  • Add the following code below the BULK INSERT block in 02_load_data.sql:
-- Populate the calendar table with a recursive CTE
WITH DateRange AS (
    SELECT MIN(transaction_date) AS date_key FROM transactions
    UNION ALL
    SELECT DATEADD(DAY, 1, date_key)
    FROM DateRange
    WHERE date_key < (SELECT MAX(transaction_date) FROM transactions)
)
INSERT INTO calendar (date_key, month_number, month_name, quarter_number, year_number)
SELECT
    date_key,
    MONTH(date_key),
    DATENAME(MONTH, date_key),
    DATEPART(QUARTER, date_key),
    YEAR(date_key)
FROM DateRange
OPTION (MAXRECURSION 400);
GO

What does this code do?

A recursive CTE is a query that calls itself repeatedly to build up a result set row by row.

  • The anchor query (SELECT MIN(transaction_date)) finds the earliest transaction date as the starting point.
  • The recursive part adds one day at a time (DATEADD(DAY, 1, date_key)) until it reaches the latest transaction date.
  • OPTION (MAXRECURSION 400) raises the default 100-iteration limit so the CTE can generate up to 400 days (about 13 months).
  • The INSERT...SELECT then extracts month, quarter, and year from each generated date.
  • Select only the new CTE code you just added, then press Ctrl+Shift+E to execute it.
  • You should see a message showing 365 rows affected (or close to that, depending on your dataset's date range).
  • Save 02_load_data.sql.
  • Verify both tables have data by running this quick check:
SELECT COUNT(*) AS transaction_count FROM transactions;
SELECT COUNT(*) AS calendar_days FROM calendar;

You should see 5000 for the transaction count and approximately 365 for the calendar days.

Calendar table showing 0 rows?

  • Make sure you ran the BULK INSERT first. The recursive CTE relies on data already existing in the transactions table to find the min and max dates.
  • If you see a MAXRECURSION error, confirm you included OPTION (MAXRECURSION 400) at the very end of the SELECT statement, before the GO.

Help me fix my calendar table CTE

✔️ Awesome, I've got everything!

Great. Double check you have saved 02_load_data.sql before moving on.

ⓧ I'd like to double check the full code

Here is the complete 02_load_data.sql file for comparison:

USE RevenueLeakage;
GO

-- Load transactions from CSV
BULK INSERT transactions
FROM 'C:\Revenue\transactions.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2,
    TABLOCK
);
GO

-- Populate the calendar table with a recursive CTE
WITH DateRange AS (
    SELECT MIN(transaction_date) AS date_key FROM transactions
    UNION ALL
    SELECT DATEADD(DAY, 1, date_key)
    FROM DateRange
    WHERE date_key < (SELECT MAX(transaction_date) FROM transactions)
)
INSERT INTO calendar (date_key, month_number, month_name, quarter_number, year_number)
SELECT
    date_key,
    MONTH(date_key),
    DATENAME(MONTH, date_key),
    DATEPART(QUARTER, date_key),
    YEAR(date_key)
FROM DateRange
OPTION (MAXRECURSION 400);
GO

Your database now holds 5,000 real transactions and a full calendar dimension. Next up, you will write diagnostic SQL queries that slice this data to reveal exactly where revenue is leaking.

Write Diagnostic SQL Queries

Your database is loaded with 5,000 transactions spanning multiple months, regions, and leakage categories. Now you need to answer the questions that matter: how much revenue is leaking, where is it going, and is the problem getting worse?

In this step, you will write three diagnostic SQL queries that slice the data by category, time, and geography. These queries use CTEs, CASE expressions, and window functions to produce the exact numbers your Power BI dashboard will visualize later.

In this step, get ready to:
  • Write a leakage summary query that breaks down revenue loss by category.
  • Write a monthly trend query that tracks leakage over time with month-over-month change.
  • Write a regional comparison query that ranks regions by leakage rate.
Write the leakage summary query

The first question any stakeholder asks about revenue leakage is: how much are we losing, and in which categories? This query groups transactions by type and calculates the leakage amount for each.

  • Create a new file called 03_diagnostic_queries.sql in the same folder as your previous SQL files.
  • Add the database context and leakage summary query by pasting the following code:
USE RevenueLeakage;
GO

-- Query 1: Leakage summary by category
WITH LeakageSummary AS (
    SELECT
        transaction_type,
        COUNT(*) AS transaction_count,
        SUM(gross_amount) AS total_gross,
        SUM(CASE WHEN transaction_type != 'valid_sale' THEN gross_amount ELSE 0 END) AS leakage_amount
    FROM transactions
    GROUP BY transaction_type
)
SELECT
    transaction_type,
    transaction_count,
    total_gross,
    leakage_amount,
    CAST(leakage_amount * 100.0 / NULLIF(total_gross, 0) AS DECIMAL(5, 2)) AS leakage_pct
FROM LeakageSummary
ORDER BY leakage_amount DESC;
GO

What does this code do?

The CTE LeakageSummary groups all transactions by their type (valid_sale, refund, chargeback, discount_abuse, failed_payment). Inside it, the CASE WHEN expression filters: it only counts the gross_amount as leakage if the transaction type is NOT a valid sale.

The outer SELECT then calculates leakage_pct by dividing leakage by total gross. NULLIF(total_gross, 0) prevents a division-by-zero error if any category has zero gross revenue.

  • Save 03_diagnostic_queries.sql.
  • Highlight just this first query (from USE RevenueLeakage through the first GO after the SELECT) and press Ctrl+Shift+E to execute it.
  • You should see a results grid with one row per transaction type, showing the transaction count, total gross, leakage amount, and leakage percentage for each category.

Seeing an error or empty results?

  • If you see "Invalid object name 'transactions'", make sure you highlighted the USE RevenueLeakage; GO lines at the top. Without them, the query runs against the wrong database.
  • If you get zero rows, confirm your data loaded successfully in the previous step by running SELECT COUNT(*) FROM transactions. You should see 5000.

Still stuck? Help me debug my leakage summary query in SQL Server

Write the monthly trend query

Knowing totals is useful, but executives want to know the direction. Is leakage growing or shrinking month to month? The LAG window function lets you compare each month against its predecessor without a self-join.

  • In 03_diagnostic_queries.sql, add the following query below the first GO statement:
-- Query 2: Monthly trend with month-over-month change
WITH MonthlyLeakage AS (
    SELECT
        YEAR(transaction_date) AS yr,
        MONTH(transaction_date) AS mo,
        DATENAME(MONTH, transaction_date) AS month_name,
        SUM(CASE WHEN transaction_type != 'valid_sale' THEN gross_amount ELSE 0 END) AS leakage_amount,
        SUM(gross_amount) AS total_revenue
    FROM transactions
    GROUP BY YEAR(transaction_date), MONTH(transaction_date), DATENAME(MONTH, transaction_date)
)
SELECT
    yr,
    mo,
    month_name,
    leakage_amount,
    total_revenue,
    CAST(leakage_amount * 100.0 / NULLIF(total_revenue, 0) AS DECIMAL(5, 2)) AS leakage_rate,
    LAG(leakage_amount) OVER (ORDER BY yr, mo) AS prev_month_leakage,
    CAST(
        (leakage_amount - LAG(leakage_amount) OVER (ORDER BY yr, mo)) * 100.0
        / NULLIF(LAG(leakage_amount) OVER (ORDER BY yr, mo), 0)
    AS DECIMAL(5, 2)) AS mom_change_pct
FROM MonthlyLeakage
ORDER BY yr, mo;
GO

What does this code do?

The CTE groups transactions by year and month, summing both leakage and total revenue. The outer SELECT then uses LAG(leakage_amount) OVER (ORDER BY yr, mo) to peek at the previous month's leakage without needing a self-join.

The mom_change_pct column calculates the percentage difference between the current and previous month. A positive number means leakage grew. A negative number means it shrank. The first row will show NULL for both prev_month_leakage and mom_change_pct because there is no prior month to compare against.

  • Save 03_diagnostic_queries.sql.
  • Highlight Query 2 (from the -- Query 2 comment through its GO) and press Ctrl+Shift+E to execute.
  • You should see one row per month, ordered chronologically. The first row's prev_month_leakage and mom_change_pct columns should be NULL.

Seeing a syntax error near LAG?

  • Make sure the OVER (ORDER BY yr, mo) clause immediately follows each LAG() call. Window functions require an OVER clause to define ordering.
  • Check that you did not accidentally include the previous query's GO in your highlight. Each batch must be executed separately.

Need more help? Help me fix my monthly trend query with LAG

Write the regional comparison query

The final diagnostic question is geographic. Some regions might have higher leakage rates due to different fraud patterns or refund policies. RANK() lets you order regions by severity so leadership knows where to focus.

  • In 03_diagnostic_queries.sql, add the following query below Query 2's GO statement:
-- Query 3: Regional comparison ranked by leakage rate
WITH RegionalLeakage AS (
    SELECT
        region,
        SUM(gross_amount) AS total_revenue,
        SUM(CASE WHEN transaction_type != 'valid_sale' THEN gross_amount ELSE 0 END) AS leakage_amount,
        CAST(
            SUM(CASE WHEN transaction_type != 'valid_sale' THEN gross_amount ELSE 0 END) * 100.0
            / NULLIF(SUM(gross_amount), 0)
        AS DECIMAL(5, 2)) AS leakage_rate
    FROM transactions
    GROUP BY region
)
SELECT
    region,
    total_revenue,
    leakage_amount,
    leakage_rate,
    RANK() OVER (ORDER BY leakage_rate DESC) AS leakage_rank
FROM RegionalLeakage
ORDER BY leakage_rank;
GO

What does this code do?

The CTE calculates each region's total revenue, leakage amount, and leakage rate in one pass. Computing leakage_rate inside the CTE means the outer query can rank on it directly.

RANK() OVER (ORDER BY leakage_rate DESC) assigns rank 1 to the region with the highest leakage rate. If two regions tie, they share the same rank and the next rank is skipped.

  • Save 03_diagnostic_queries.sql.
  • Highlight Query 3 (from the -- Query 3 comment through its GO) and press Ctrl+Shift+E to execute.
  • You should see one row per region, ranked from worst leakage rate (rank 1) to best. The top row is the region that needs the most attention.

All regions showing the same leakage rank?

  • If every region has rank 1, check that you wrote ORDER BY leakage_rate DESC inside the OVER clause. Without DESC, all regions with a non-zero rate could tie at rank 1 if the ordering is ascending.
  • Double-check that the CASE expression uses != 'valid_sale' (not equals). Using = 'valid_sale' would count valid sales as leakage instead.

Still not getting expected results? Help me debug my regional ranking query

Why CTEs instead of subqueries?

CTEs make complex queries readable by giving each logical step a name. You can reference the CTE name in the outer SELECT like a table, which is easier to debug than nested subqueries three levels deep.

They also make your queries easier to hand off to colleagues. A finance team member can read RegionalLeakage and understand what that intermediate result contains without tracing parentheses.

✔️ Awesome, I've got everything!

Great. Double check you have saved 03_diagnostic_queries.sql with all three queries.

ⓧ I'd like to double check the full code

Here is the complete 03_diagnostic_queries.sql file with all three diagnostic queries:

USE RevenueLeakage;
GO

-- Query 1: Leakage summary by category
WITH LeakageSummary AS (
    SELECT
        transaction_type,
        COUNT(*) AS transaction_count,
        SUM(gross_amount) AS total_gross,
        SUM(CASE WHEN transaction_type != 'valid_sale' THEN gross_amount ELSE 0 END) AS leakage_amount
    FROM transactions
    GROUP BY transaction_type
)
SELECT
    transaction_type,
    transaction_count,
    total_gross,
    leakage_amount,
    CAST(leakage_amount * 100.0 / NULLIF(total_gross, 0) AS DECIMAL(5, 2)) AS leakage_pct
FROM LeakageSummary
ORDER BY leakage_amount DESC;
GO

-- Query 2: Monthly trend with month-over-month change
WITH MonthlyLeakage AS (
    SELECT
        YEAR(transaction_date) AS yr,
        MONTH(transaction_date) AS mo,
        DATENAME(MONTH, transaction_date) AS month_name,
        SUM(CASE WHEN transaction_type != 'valid_sale' THEN gross_amount ELSE 0 END) AS leakage_amount,
        SUM(gross_amount) AS total_revenue
    FROM transactions
    GROUP BY YEAR(transaction_date), MONTH(transaction_date), DATENAME(MONTH, transaction_date)
)
SELECT
    yr,
    mo,
    month_name,
    leakage_amount,
    total_revenue,
    CAST(leakage_amount * 100.0 / NULLIF(total_revenue, 0) AS DECIMAL(5, 2)) AS leakage_rate,
    LAG(leakage_amount) OVER (ORDER BY yr, mo) AS prev_month_leakage,
    CAST(
        (leakage_amount - LAG(leakage_amount) OVER (ORDER BY yr, mo)) * 100.0
        / NULLIF(LAG(leakage_amount) OVER (ORDER BY yr, mo), 0)
    AS DECIMAL(5, 2)) AS mom_change_pct
FROM MonthlyLeakage
ORDER BY yr, mo;
GO

-- Query 3: Regional comparison ranked by leakage rate
WITH RegionalLeakage AS (
    SELECT
        region,
        SUM(gross_amount) AS total_revenue,
        SUM(CASE WHEN transaction_type != 'valid_sale' THEN gross_amount ELSE 0 END) AS leakage_amount,
        CAST(
            SUM(CASE WHEN transaction_type != 'valid_sale' THEN gross_amount ELSE 0 END) * 100.0
            / NULLIF(SUM(gross_amount), 0)
        AS DECIMAL(5, 2)) AS leakage_rate
    FROM transactions
    GROUP BY region
)
SELECT
    region,
    total_revenue,
    leakage_amount,
    leakage_rate,
    RANK() OVER (ORDER BY leakage_rate DESC) AS leakage_rank
FROM RegionalLeakage
ORDER BY leakage_rank;
GO

You now have three production-quality SQL queries that quantify your revenue leakage from every angle. Next up, you will connect Power BI to this database and turn these numbers into an interactive dashboard that stakeholders can explore themselves.

Connect Power BI to Your SQL Database

Your SQL queries can now quantify exactly where revenue leaks. But those query results live in a terminal window that only you can see.

In this step, you will connect Power BI Desktop to your SQL Server database, import your tables, and create DAX measures that power the dashboard visuals you will build next.

In this step, get ready to:
  • Connect Power BI Desktop to your LocalDB instance.
  • Import the transactions and calendar tables and set up a relationship between them.
  • Create DAX measures for Total Revenue, Total Leakage, and Leakage Rate.
Open Power BI and connect to LocalDB

Power BI Desktop needs a direct connection to your SQL Server database so it can pull in the transaction data for visualization.

  • Press the Windows key to open your search bar.
  • Type Power BI Desktop and press Enter to open it.
  • On the Home ribbon, click Get Data.
  • In the Get Data window, select Database from the left panel, then select SQL Server database.
  • Click Connect.

A SQL Server database dialog appears asking for your server and database details.

  • In the Server field, enter (localdb)\MSSQLLocalDB.
  • In the Database (optional) field, enter RevenueLeakage.
  • Under Data Connectivity mode, make sure Import is selected (this is the default).
  • Click OK.

Why Import mode instead of DirectQuery?

Import mode loads all the data into Power BI's in-memory engine. This makes your dashboard fast and responsive, even when LocalDB is not actively running.

DirectQuery would require LocalDB to be online every time someone opens the report. For a 5,000-row dataset, Import is the better fit.

Seeing a connection error?

Make sure you typed the server name exactly as (localdb)\MSSQLLocalDB including the backslash and parentheses. If the error persists, open a command prompt and run sqllocaldb start MSSQLLocalDB to ensure the instance is running.

Why can't Power BI find my LocalDB instance?

Load tables and create a relationship

Power BI needs both your data table and your calendar table to support time-based analysis. A relationship between them tells Power BI how to link dates to transactions.

  • In the Navigator window that appears, check the box next to transactions.
  • Check the box next to calendar.
  • Click Load to import both tables into the Power BI model.

You should see both tables appear in the Fields pane on the right side of the Power BI window.

Now you need to create a relationship so Power BI knows which calendar date corresponds to each transaction.

  • Click the Model view icon in the left sidebar (it looks like a diagram with connected boxes).
  • You should see both the transactions and calendar tables displayed as boxes.
  • Click and drag transaction_date from the transactions table onto date_key in the calendar table.

A line now connects the two tables in the Model view. This creates a many-to-one relationship (many transactions can share one calendar date).

What does this relationship do?

The relationship between transactions[transaction_date] and calendar[date_key] allows Power BI to slice transaction data by month, quarter, or year using the calendar table's columns.

Without this link, your time-based charts would have no way to group transactions into meaningful time periods.

Don't see both tables in Model view?

If only one table loaded, go back to Home > Get Data > SQL Server database and repeat the connection steps. Make sure you check both table checkboxes in the Navigator before clicking Load.

Help me fix my Power BI table import

Create DAX measures for leakage metrics

DAX measures are formulas that calculate values dynamically based on whatever filters a user applies to the dashboard. Unlike a static SQL result, a DAX measure recalculates when someone clicks a slicer or drills into a chart.

  • Switch back to the Report view by clicking the top icon in the left sidebar (the chart icon).
  • In the Fields pane on the right, right-click the transactions table.
  • Select New measure.
  • In the formula bar that appears at the top, type the following and press Enter:
Total Revenue = SUM(transactions[gross_amount])

What does this measure do?

This sums up the gross_amount column across all visible transactions. When a user filters by region or date, this number automatically adjusts to show only that subset's total.

  • Right-click the transactions table again and select New measure.
  • Enter this formula for Total Leakage:
Total Leakage = CALCULATE(SUM(transactions[gross_amount]), transactions[transaction_type] <> "valid_sale")

What does CALCULATE do here?

CALCULATE evaluates an expression (the SUM) in a modified filter context. The filter transactions[transaction_type] <> "valid_sale" excludes legitimate sales, leaving only refunds, chargebacks, discount abuse, and failed payments.

  • Create another new measure on the transactions table.
  • Enter this formula for Leakage Rate:
Leakage Rate = DIVIDE([Total Leakage], [Total Revenue], 0)

Why use DIVIDE instead of a slash?

The DIVIDE function handles division-by-zero gracefully by returning 0 (the third argument) instead of an error. This protects your dashboard from crashing if a filter combination produces zero total revenue.

  • Create one more new measure on the transactions table.
  • Enter this formula for Average Transaction Size:
Avg Transaction Size = AVERAGE(transactions[gross_amount])
  • Verify your measures by expanding the transactions table in the Fields pane. You should see all four measures listed with a calculator icon next to each name.

Save your reference file with all the DAX formulas in case you need them later. Create a file called dax_measures.txt in the same folder as your SQL files with the following content:

Total Revenue = SUM(transactions[gross_amount])

Total Leakage = CALCULATE(SUM(transactions[gross_amount]), transactions[transaction_type] <> "valid_sale")

Leakage Rate = DIVIDE([Total Leakage], [Total Revenue], 0)

Avg Transaction Size = AVERAGE(transactions[gross_amount])

What does this file do?

This is a reference file for your DAX formulas. Power BI stores measures inside the .pbix file, so keeping a plain-text copy makes it easy to review or recreate them later.

  • Save your Power BI file by pressing Ctrl+S. Name it RevenueLeakageDashboard.pbix and save it in the same folder as your SQL files.

Measure showing an error?

Check that you typed the table and column names exactly as they appear in the Fields pane. DAX is case-insensitive, but the square brackets and table references must match your imported column names precisely.

If you see a "circular dependency" error, make sure you are referencing [Total Leakage] and [Total Revenue] (measure names in brackets) in your Leakage Rate formula, not the column names.

Help me fix my DAX measure error

✔️ Awesome, I've got everything!

Make sure you have saved both your Power BI file and your reference text file.

ⓧ I'd like to double check the full code

Here are all four DAX measures that should be in your Power BI model and reference file:

Total Revenue = SUM(transactions[gross_amount])

Total Leakage = CALCULATE(SUM(transactions[gross_amount]), transactions[transaction_type] <> "valid_sale")

Leakage Rate = DIVIDE([Total Leakage], [Total Revenue], 0)

Avg Transaction Size = AVERAGE(transactions[gross_amount])

Your data model is ready. You have a connected database, a proper date relationship, and dynamic measures that respond to user filters. Next up, you will build the actual dashboard visuals that bring these numbers to life.

Build the Dashboard Visuals

In the previous step, you connected Power BI to your SQL database and created DAX measures that calculate leakage metrics dynamically. Now it is time to turn those measures into something a stakeholder can actually read at a glance.

In this step, you will build the visual layer of your dashboard. You will create KPI cards, a category breakdown chart, a time-series trend line, and a region filter. Together, these visuals answer the core questions executives ask: how much revenue are we losing, where is it going, and is it getting better or worse?

In this step, get ready to:
  • Create a KPI summary row with Card visuals and conditional formatting.
  • Build a stacked bar chart and a line chart to show leakage by category and trend over time.
  • Add a Region slicer and a top-transactions table to complete the dashboard.
Create the KPI summary row

Executives scan dashboards top-to-bottom. The first thing they need to see is the headline numbers: total revenue, total leakage, and the leakage rate percentage.

  • In Power BI Desktop, make sure you are on the main report page (Page 1).
  • Click an empty area of the canvas so no existing visual is selected.
  • In the Visualizations pane on the right, select the Card visual icon (a single number card).
  • In the Data pane, expand transactions and drag the Total Revenue measure into the Fields well of the Card visual.
  • Resize and position this card in the top-left area of the canvas.

Now repeat this to create two more Card visuals for Total Leakage and Leakage Rate.

  • Click an empty area of the canvas again.
  • Select the Card visual icon in the Visualizations pane.
  • Drag the Total Leakage measure into the Fields well.
  • Position this card to the right of the Total Revenue card.
  • Repeat one more time for the Leakage Rate measure, positioning it to the right of Total Leakage.

You should now see three Card visuals in a row at the top of your report canvas showing your headline KPI numbers.

Why Card visuals for KPIs?

Card visuals display a single measure value in a large, readable format. Placing them in a row gives stakeholders an instant summary without needing to interpret a chart. This is the standard pattern for executive dashboards.

Next, add conditional formatting to the Leakage Rate card so it turns red when the rate exceeds 5%.

  • Select the Leakage Rate Card visual on the canvas.
  • In the Visualizations pane, click the Format visual icon (the paint brush).
  • Expand the Callout value section under the Visual tab.
  • Find the Color option and click the fx button next to it to open the conditional formatting dialog.
  • Set Format style to Rules.
  • In the rule, set: If value is greater than or equal to 0.05 Number, then set the color to red.
  • Click OK to apply.

Why 0.05 and not 5?

The Leakage Rate measure uses DIVIDE, which returns a decimal (e.g., 0.08 for 8%). When setting rules for percentage-based fields, enter the value as a decimal and select Number as the format. Entering 5 with the Percent format would produce unexpected results.

Your Leakage Rate card should now display the number in red if the leakage rate is above 5%.

Card not turning red?

Make sure you selected the fx button next to the Color option under Callout value, not under a different section. Also confirm the rule uses 0.05 with the Number format, not 5 with Percent.

Help me fix conditional formatting on my Card visual

Build the category breakdown and trend charts

Knowing the total leakage number is useful, but stakeholders need to see which categories contribute the most and whether the problem is improving over time. A stacked bar chart answers the first question. A line chart answers the second.

  • Click an empty area of the canvas below your KPI cards.
  • In the Visualizations pane, select the Stacked bar chart icon.
  • From the Data pane, drag transaction_type from the transactions table to the Y-axis well.
  • Drag the Total Leakage measure to the X-axis well.
  • Resize the chart so it occupies roughly the left half of the space below the KPI row.

You should see horizontal bars showing the leakage amount for each transaction type (refund, chargeback, discount_abuse, failed_payment). The valid_sale type shows zero leakage because the Total Leakage measure filters it out.

Now create the monthly trend line chart.

  • Click an empty area of the canvas to the right of the bar chart.
  • In the Visualizations pane, select the Line chart icon.
  • From the Data pane, expand the calendar table and drag month_name to the X-axis well.
  • Drag the Total Leakage measure to the Y-axis well.
  • Resize this chart to fill the right half of the space below the KPI row.

Why use the calendar table for the X-axis?

Using calendar[month_name] instead of extracting the month directly from transactions[transaction_date] ensures consistent date ordering and leverages the relationship you built between the two tables. This is a best practice in Power BI data modeling.

Your line chart should now display a trend showing how monthly leakage moves over time.

Months out of order?

If months appear alphabetically instead of chronologically, you need to sort the axis. Click the More options menu (the three dots in the top-right corner of the line chart), select Sort axis, then choose month_number from the calendar table. If month_number is not available, add it to the X-axis well as a sort-by column via the column tools in the Data pane.

Help me sort months chronologically in my Power BI line chart

Add the Region slicer and top transactions table

A slicer lets stakeholders filter the entire dashboard by a single dimension. Adding a Region slicer means they can instantly see leakage data for one geography at a time. A table visual showing the top individual transactions provides the granular detail behind the headlines.

  • Click an empty area of the canvas.
  • In the Visualizations pane, select the Slicer visual icon.
  • From the Data pane, drag region from the transactions table into the Field well of the slicer.
  • Position the slicer along the left or right side of your report where it is easy to access.

Click different region values in the slicer. You should see all the other visuals on the page update instantly to reflect the selected region.

Now create the top transactions table.

  • Click an empty area of the canvas below the bar chart and line chart.
  • In the Visualizations pane, select the Table visual icon.
  • From the Data pane, drag these fields from transactions into the Columns well: transaction_id, transaction_date, gross_amount, transaction_type, region.
  • Sort the table by gross_amount descending: click the gross_amount column header in the visual until it sorts from highest to lowest.
  • To limit the table to the top 10 transactions, open the Filters pane on the right.
  • Under Filters on this visual, expand gross_amount.
  • Change the filter type to Top N.
  • Set Show items to Top 10.
  • Drag gross_amount into the By value field in the filter, then click Apply filter.

Your table now shows only the 10 highest-value leakage transactions sorted by amount.

What is a Top N filter?

Top N is a visual-level filter in Power BI that limits displayed rows to the highest (or lowest) values based on a measure. It is perfect for surfacing the biggest problem transactions without cluttering the table with thousands of rows.

Finally, create one more DAX measure that will be useful for deeper analysis.

  • In the Data pane, right-click the transactions table and select New measure.
  • Enter the following formula in the formula bar:
Avg Transaction Size = AVERAGE(transactions[gross_amount])
  • Press Enter to confirm the measure.

What does this measure do?

Avg Transaction Size calculates the average gross amount across all transactions in the current filter context. You will use this in the Secret Mission to show average transaction size on a detail page.

Save your Power BI file. Your completed dashboard now has a KPI row, a category chart, a trend line, a region slicer, and a top-transactions table.

Slicer not filtering other visuals?

By default, slicers filter all visuals on the same page. If a visual is not responding to the slicer, select the slicer and go to Format > Edit interactions in the ribbon. Make sure the filter icon is active for each visual you want affected.

Help me fix my Power BI slicer interactions

Your dashboard is complete. Next up, you will tackle the Secret Mission and add a drill-through page that lets stakeholders investigate individual transactions behind each leakage category.

Secret mission

Add a Drill-Through Detail Page

Your dashboard gives executives the big picture. But what happens when a stakeholder spots a spike in chargebacks and wants to see the actual transactions? Build a drill-through page that lets users right-click any leakage category and instantly see every flagged transaction behind it.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing costs.

Resources you used:

  • SQL Server Express LocalDB instance (MSSQLLocalDB).
  • RevenueLeakage database with transactions and calendar tables.
  • SQL query files (01_create_database.sql, 02_load_data.sql, 03_diagnostic_queries.sql).
  • Sample CSV file (transactions.csv).
  • Power BI report file (.pbix).
  • VS Code mssql extension.

Keep everything running

No action needed. Choose this if you are still actively building or plan to extend the dashboard further.

  • Your LocalDB instance stays available and auto-starts whenever you connect.
  • Your Power BI report and SQL files remain on disk ready for editing.
  • LocalDB uses no resources when idle, so there is no performance impact.

Pause - I'll come back to this later

Shut down the LocalDB instance to free up memory. Your database and files stay intact for when you return.

  • Open a command prompt and run sqllocaldb stop MSSQLLocalDB to stop the instance.
  • Close Power BI Desktop to free up memory.
  • Your database, SQL files, and Power BI report remain on disk.
  • To resume later, simply reconnect from VS Code or Power BI. LocalDB auto-starts on connection.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow these steps to clean everything up.

  • Open a command prompt and connect to LocalDB. Run DROP DATABASE RevenueLeakage to remove the database.
  • Delete your SQL query files (01_create_database.sql, 02_load_data.sql, 03_diagnostic_queries.sql).
  • Delete the transactions.csv file and the C:\Revenue folder.
  • Delete your Power BI report file (.pbix).
  • Optionally uninstall the mssql extension from VS Code if you no longer need it.
  • Optionally uninstall SQL Server Express LocalDB from Settings > Apps > Apps & features if you do not plan to use it for other projects.

Nice Work!

Nice Work!

You just built a complete revenue leakage reporting pipeline, from raw CSV data all the way to a stakeholder-ready Power BI dashboard.

You've learned how to:

  • Design a SQL Server database schema and load transaction data using BULK INSERT and a recursive CTE for date generation.
  • Write production-quality diagnostic queries using CTEs, CASE expressions, and window functions (LAG, RANK) to quantify revenue leakage by category, time period, and region.
  • Connect Power BI to a SQL database, create DAX measures for dynamic KPIs, and build an interactive dashboard with cards, charts, slicers, and conditional formatting.
  • Added a drill-through detail page so stakeholders can click on a leakage category and investigate individual flagged transactions.

Ready to quiz yourself?