Create:
Business_Data_Model.xlsx
Table 1 — Sales
|
OrderID |
Date |
CustomerID |
ProductID |
EmployeeID |
Quantity |
Sales Amount |
|
O101 |
01-Jan-26 |
C101 |
P101 |
E101 |
2 |
120000 |
|
O102 |
05-Jan-26 |
C102 |
P102 |
E102 |
10 |
5000 |
|
O103 |
10-Feb-26 |
C101 |
P103 |
E101 |
3 |
102000 |
|
O104 |
15-Feb-26 |
C103 |
P102 |
E103 |
8 |
4000 |
|
O105 |
20-Mar-26 |
C104 |
P101 |
E102 |
1 |
60000 |
|
O106 |
25-Mar-26 |
C102 |
P104 |
E104 |
4 |
10000 |
Table 2 — Products
|
ProductID |
Product |
Category |
|
P101 |
Laptop |
Electronics |
|
P102 |
Mouse |
Accessories |
|
P103 |
Monitor |
Electronics |
|
P104 |
Webcam |
Accessories |
Table 3 — Customers
|
CustomerID |
Customer |
City |
|
C101 |
Amit |
Sirsa |
|
C102 |
Neha |
Hisar |
|
C103 |
Priya |
Fatehabad |
|
C104 |
Rahul |
Sirsa |
Table 4 — Employees
|
EmployeeID |
Employee |
Department |
|
E101 |
Amit |
Sales |
|
E102 |
Neha |
Sales |
|
E103 |
Priya |
Sales |
|
E104 |
Simran |
Sales |
Table 5 — Calendar
Create a proper date table containing dates covering the sales period.
Suggested columns:
|
Date |
Year |
Month |
Month Number |
Quarter |
|
01-Jan-26 |
2026 |
January |
1 |
Q1 |
|
02-Jan-26 |
2026 |
January |
1 |
Q1 |
|
… |
… |
… |
… |
… |
PROJECT TASKS
Step 1 — Load Tables
Add all five tables to the Data Model.
Step 2 — Create Relationships
Create:
Products[ProductID] → Sales[ProductID]
Customers[CustomerID] → Sales[CustomerID]
Employees[EmployeeID] → Sales[EmployeeID]
Calendar[Date] → Sales[Date]
Step 3 — Create Calculated Column
Product Category :=
RELATED(Products[Category])
Step 4 — Create Measures
Total Sales :=
SUM(Sales[Sales Amount])
Total Quantity :=
SUM(Sales[Quantity])
Unique Customers :=
DISTINCTCOUNT(Sales[CustomerID])
Step 5 — Create Analysis Measures
Sales YTD :=
TOTALYTD(
[Total Sales],
Calendar[Date]
)
Sales MTD :=
TOTALMTD(
[Total Sales],
Calendar[Date]
)
Sales Previous Year :=
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR(Calendar[Date])
)
Step 6 — Create PivotTable
Analyze:
- Sales by Product
- Sales by Category
- Sales by City
- Sales by Employee
- Monthly Sales
- YTD Sales
- Previous Year Sales
Step 7 — Create Business Report
Final report should contain:
Total Sales | Total Quantity | Unique Customers | Sales YTD
plus product, customer, employee and time-based analysis.