Tailwind Traders Capstone Project

Overview

Your tasks in these exercises were to:

• Prepare Sales Excel data.

• Configure data sources.

• Design and develop the data model

• Configure aggregations using DAX.

• Create sales and profit reports.

• Create an executive dashboard.

Exercise 1: Prepare the Sales Excel data

Step 1: Prepare Sales Data:

Open Tailwind Traders Sales.xlsx.

• Calculate Gross Revenue
• Calculate Total Tax
• Calculate Net Revenue
• Configure Data Sources in Power BI

Step 2: Load Tailwind Traders Sales.xlsx.

• Assign data types.
• Validate data integrity.
• Design and Develop Data Model

Step 3: Create a Calendar table with DAX.

• Configure the Calendar table
• Select New Table and add the following DAX code to create a new Calendar table:

CalendarTable = 
ADDCOLUMNS(
CALENDAR(DATE(2020, 1, 1), DATE(2023, 12, 31)),
"Year", YEAR([Date]),
"Month Number", MONTH([Date]),
"Month", FORMAT([Date], "MMMM"),
"Quarter", QUARTER([Date]),
"Weekday", WEEKDAY([Date]),
"Day", DAY([Date])
)

Step 4: Create Sales in USD Calculated Table

Select New Table and add the following DAX code to create a new calculated table:

Sales in USD = 
ADDCOLUMNS(
    Sales,
    "Country Name", RELATED(Countries[Country]),
    "Exchange Rate", RELATED('Exchange Data'[Exchange Rate]),
    "Exchange Currency", RELATED('Exchange Data'[Exchange Currency]),
    "Gross Revenue USD", [Gross Revenue] * RELATED('Exchange Data'[Exchange Rate]),
    "Net Revenue USD", [Net Revenue] * RELATED('Exchange Data'[Exchange Rate]),
    "Total Tax USD", [Total Tax] * RELATED('Exchange Data'[Exchange Rate])
)

Step 5: Load Currency Exchange data

Select Get Data, choose Python script, and then paste the following code into the script window in Power BI:

Exercise 2: Design and develop the data model

Exercise 3: Configure aggregations using DAX

Step 1: Calculate Yearly Profit margin

This calculation divides the Gross Revenue by the Net Revenue, and shows how much money is kept as profit from sales after costs. This measure provides an overarching view of Tailwind Traders’ financial efficiency throughout the year and is a key indicator of the company's financial health.

Step 2: Calculate Quarterly Profit

This calculation isolates the profit made in each quarter by using a time intelligence function, which filters the profit to each respective quarter. This data provides actionable insights for short-term planning and strategy.

Step 3: Calculate Year-to-Date Profit

This measure accumulates the profit from the first day of the fiscal year to the current date, providing a running total of Tailwind Traders' profitability. This step provides a real-time snapshot of the company’s financial trajectory, allowing for comparison against the same period in previous years or projected targets.

Step 4: Calculate Median Sales

The median is a statistical measure that indicates the middle value in a set of numbers, which, in this case, represents sales volumes. Using the MEDIAN function on Gross Revenue identifies the sales value at the center of the dataset.

Exercise 4: Create a Sales report

• Step 1: Create a bar chart for loyalty points by country

• Step 2: Create a column chart for Quantity Sold by Product

• Step 3: Create a pie chart for median sales distribution by country

• Step 4: Create a line chart for median sales over time

• Step 5: Create cards that visualize the following measures:

  a) Stock.

  b) Quantity Purchased.

  c) Median Sales.

• Step 6: Create a slicer that displays the Country Name data from the Sales in USD table.

Sales Report Snapshot

Exercise 5: Create a Profit report

• Step 1: Create a bar chart for Net Revenue by Product

• Step 2: Create a donut chart for Yearly Profit Margin by Country

• Step 3: Create an area chart for Yearly Profit Margin over Time

• Step 4: Create cards to visualize your measures

  a) YTD Profit

  b) Net Revenue USD

• Step 5: Create a KPI for Gross Revenue USD

• Step 6: Create a slicer that displays the Datedata from the CalendarTable table.

Profit report Snapshot