POWER QUERY STEPS:
Load Data
1. Power BI Desktop → Home → Get Data → Excel
2. Select your file IDS_DATA.xlsx
3. Select all required sheets (Sales, Employees, Products, Customers, etc.)
4. Click Transform Data
Use First Row as Headers
1. Power Query Editor → Home tab → Use First Row as Headers
Remove Unnecessary Rows/Columns
1. Right-click any blank or unwanted columns → Remove Columns
2. Remove extra empty rows → Home → Remove Rows → Remove Blank Rows
Change Data Types
1. Each column ka type sahi set karo (Date, Text, Whole Number, Decimal Number)
2. Example: SalesAmount → Decimal, HireDate → Date
Trim & Clean Text Columns
1. Select all Text columns → Transform tab → Format → Trim
2. Then again Transform tab → Format → Clean
Remove Duplicates
1. Select unique identifier column → Home → Remove Rows → Remove Duplicates
Rename Queries
1. Rename each sheet as: Sales, Employees, Products, Customers, Countries, Sales_Date, etc.
Close & Load
1. After cleaning all sheets → Close & Apply
POWER BI STEPS
Create Date Table:
Go to Modeling → New Table and paste this DAX
Date_Table =
VAR StartDate = DATE(2022,7,1)
VAR EndDate = DATE(2025,6,30)
RETURN
ADDCOLUMNS (
CALENDAR ( StartDate, EndDate ),
"MonthName", FORMAT ( [Date], "MMMM" ),
"MonthNumber", MONTH ( [Date] ),
"QuarterNumber", "Q" & FORMAT ( [Date], "Q" ),
"CalendarYear", YEAR ( [Date] ),
"FinancialYear",
IF (
MONTH ( [Date] ) >= 7,
FORMAT ( YEAR ( [Date] ), "0000" ) & "-" & FORMAT ( YEAR ( [Date] ) + 1, "0000" ),
FORMAT ( YEAR ( [Date] ) - 1, "0000" ) & "-" & FORMAT ( YEAR ( [Date] ), "0000" )
)
)
No error, no duplicate column problem
Continuous date range July 2022 → June 2025
Relationships
Go to Model View
Drag and drop:
1. Date_Table[Date] → Sales[SalesDate]
2. Products[Product_ID] → Sales[Product_ID]
3. Customers[Customer_ID] → Sales[Customer_ID]
4. Employees[Employee_ID] → Sales[Employee_ID]
Make sure arrows (relationships) are one-to-many and direction single.
C. Create Visuals (Dashboards)
Product-wise profit per month (FY 2025)
1. Visual: Clustered Column Chart
2. Axis: Date_Table[MonthName]
3. Legend: Products[ProductName]
4. Value: [Profit] (DAX below)
5. Filter: Date_Table[FinancialYear] = "2024-2025"
6. Sort MonthName by MonthNumber.
Product with highest profit each Financial Year
1. Visual: Table or Matrix
2. Rows: Date_Table[FinancialYear]
3. Columns: Products[Product_ID]
4. Values: [Profit]
5. Filter: Top N = 1 by [Profit]
Employees assigned to each product segment (as at 30-Jun-2025)
1. Visual: Bar chart
2. Axis: Products[ProductSegment]
3. Values: [Employees_AsAt_2025_06_30]
Employees with expired documents (as at 30-Jun-2025)
1. Visual: Card
2. Value: [Employees_With_Expired_Docs_AsAt_2025_06_30]
Employees hired during each financial year
1. Visual: Matrix
2. Rows: Date_Table[FinancialYear]
3. Values: [Employees_Hired]
Total customers for each product type in FY 2025
1. Visual: Stacked Column Chart
2. Axis: Products[ProductType]
3. Value: [Total_Customers_FY2025]
4. Filter: Date_Table[FinancialYear] = "2024-2025"
Financial Performance Dashboard
1. Visuals:
a. Combo chart (Sales & Profit by FinancialYear)
b. Waterfall chart (Sales → Cost → Profit)
c. Cards for KPIs and % change (YoY)
Australia Profit Table
1. Visual: Matrix
2. Rows: Date_Table[FinancialYear]
3. Columns: Date_Table[MonthName]
4. Values: [Profit]
5. Filter: Sales[Country] = "Australia"
Navigation / Title Page
1. Page: “Home”
2. Add buttons for dashboards
3. Create Bookmarks for each dashboard
4. Add Back button on each page → Action = Navigate to “Home”
5. Add Filter slicers (Financial Year, Product, Region) → Sync slicers across pages.
DAX FUNCTIONS
Basic Measures
TotalSales = SUM(Sales[SalesAmount])
TotalCost = SUM(Sales[Cost])
Profit = [TotalSales] - [TotalCost]
Employee-related Measures
Employees_AsAt_2025_06_30 =
VAR Cutoff = DATE(2025,6,30)
RETURN
CALCULATE(
DISTINCTCOUNT(Employees[EmployeeID]),
FILTER(
Employees,
Employees[AssignmentStartDate] <= Cutoff &&
(ISBLANK(Employees[AssignmentEndDate]) || Employees[AssignmentEndDate] >= Cutoff)
)
)
Employees_With_Expired_Docs_AsAt_2025_06_30 =
VAR Cutoff = DATE(2025,6,30)
RETURN
CALCULATE(
DISTINCTCOUNT(Employees[EmployeeID]),
FILTER(
Employees,
(NOT ISBLANK(Employees[CNICExpiry]) && Employees[CNICExpiry] < Cutoff) ||
(NOT ISBLANK(Employees[PassportExpiry]) && Employees[PassportExpiry] < Cutoff)
)
)
Employees_Hired =
COUNTROWS(Employees)
Customer Measure
Total_Customers_FY2025 =
CALCULATE(
DISTINCTCOUNT(Customers[CustomerID]),
FILTER(
'Date_Table',
'Date_Table'[FinancialYear] = "2024-2025"
)
)
Time Intelligence Measures
MTD_Sales = TOTALMTD([TotalSales], 'Date_Table'[Date])
QTD_Sales = TOTALQTD([TotalSales], 'Date_Table'[Date])
YTD_Sales = TOTALYTD([TotalSales], 'Date_Table'[Date])
Sales_Same_Period_Last_Year =
CALCULATE([TotalSales], SAMEPERIODLASTYEAR('Date_Table'[Date]))
Australia Profit Table (optional DAX Table)
AustraliaProfitTable =
SUMMARIZE(
FILTER(Sales, Sales[Country] = "Australia"),
'Date_Table'[FinancialYear],
'Date_Table'[MonthName],
"Profit", SUM(Sales[Profit])
)