The 4-Month Data Analyst Roadmap: Learn Excel, Power BI & DAX with AI.

Power BI Mastery Program

Beginner → Intermediate → Advanced → Expert (Duration: 8-12 weeks if you study 1-2 hours daily)

Phase 1: Power BI Beginner (Week 1-2)

Lesson 1: What is Power BI?

Power BI is Microsoft's Business Intelligence (BI) tool used to:

✅ Connect data

✅ Clean data

✅ Analyze data

✅ Build dashboards

✅ Share reports

Companies use it for:

• Operations reporting

• KPI tracking

• Financial analysis

• Risk monitoring

• Workforce analytics

Components

Power BI Desktop

Used for creating reports.

Power BI Service

Used for sharing reports online.

Power BI Mobile

View reports on phone.

Power Query

Data cleaning tool.

DAX

Formula language.

Data Model

Relationships between tables.

Exercise 1

Download Power BI Desktop (Free).

Get sample Excel data:

Employee	Team	Month	Sales
John	A	Jan	1000
Sara	B	Jan	1200
Mike	A	Feb	1500

Tasks:

1. Import Excel

2. Create bar chart

3. Create pie chart

4. Create table visual

Quiz 1

Q1. Power BI is mainly used for?

A. Coding

B. Database creation

C. Data visualization

D. Gaming

✅ Answer: C

Q2. Which tool cleans data?

A. Power Query

B. PowerPoint

C. Excel VBA

D. SharePoint

✅ Answer: A

Phase 2: Data Preparation (Week 2)

Lesson 2: Power Query

Most real-world data is messy.

Example:

Name

Nehazul

Nehazul

NULL

You need to:

• Remove duplicates

• Handle blanks

• Split columns

• Merge files

Common Transformations

Remove Duplicates

Home → Remove Rows

Split Columns

Transform → Split Column

Change Data Types

Transform → Data Type

Exercise 2

Import:

Name	City
Ruhid	Bangalore
Ruhid	Bangalore
John	Delhi

Tasks:

• Remove duplicate records

• Replace blanks

• Rename columns

Phase 3: Data Modeling (Week 3)

Lesson 3: Relationships

Example:

Employees Table

EmployeeID	Name
1	John
2	Sara

Salary Table

EmployeeID	Salary
1	50000
2	60000

Connect:

Employees[EmployeeID]

↓

Salary[EmployeeID]

Types

One to Many

Most common.

Many to Many

Advanced.

One to One

Rare.

Exercise 3

Create relationship between:

Orders

Customers

Products

Quiz 2

What creates a data model?

A. Visuals

B. Relationships

C. Colors

D. Themes

✅ B

Phase 4: DAX (Week 4-5)

This is where analysts make money.

Lesson 4: Basic DAX

Total Sales

DAX

Total Sales =

SUM(Sales[Amount])

Average Sales

DAX

Average Sales =

AVERAGE(Sales[Amount])

Count Orders

DAX

Order Count =

COUNT(Sales[OrderID])

Exercise 4

Build:

1. Total Revenue

2. Total Orders

3. Average Order Value

Lesson 5: DAX Intermediate

IF Statement

DAX

Performance =

IF([Sales] > 10000,

"Good",

"Need Improvement")

Profit %

DAX

Profit % =

DIVIDE(

[Profit],

[Sales]

)

Year-To-Date

DAX

Sales YTD =

TOTALYTD(

[Sales],

Calendar[Date]

)

Quiz 3

What is DAX?

A. Visualization

B. Formula Language

C. Database

D. Server

✅ B

Phase 5: Dashboard Building (Week 5-6)

Lesson 6: KPI Dashboard

Every dashboard should answer:

1. What happened?

2. Why happened?

3. What action needed?

Important Visuals

Cards

• Revenue

• Profit

• Customer Count

Charts

• Bar

• Line

• Area

• Funnel

Slicers

• Date

• Region

• Team

Project 1

Create Sales Dashboard

Requirements:

✅ Total Sales

✅ Total Profit

✅ Monthly Trend

✅ Top Products

✅ Region Filter

Phase 6: Advanced DAX (Week 6-7)

Lesson 7: CALCULATE Function

Most important DAX function.

DAX

Sales India =

CALCULATE(

SUM(Sales[Amount]),

Sales[Country]="India"

)

Lesson 8: Time Intelligence

Previous Month

DAX

Previous Month Sales =

CALCULATE(

[Total Sales],

PREVIOUSMONTH(

Calendar[Date]

))

``

Growth %

DAX

Growth % =

DIVIDE(

[Current]-[Previous],

[Previous]

)

Exercise 5

Build:

• MTD

• QTD

• YTD

• Growth %

Measures

Phase 7: Advanced Analytics (Week 7-8)

Lesson 9: Row Context vs Filter Context

Interview favorite.

Understand:

DAX

SUMX()

vs

DAX

SUM()

Example

DAX

Profit =

SUMX(

Sales,

Sales[Revenue] -

Sales[Cost]

)

Exercise 6

Calculate:

Revenue

Cost

Profit

Margin %

using SUMX()

Phase 8: Expert Level (Week 8-12)

Lesson 10: Real Business Scenarios

Exception Operations Dashboard

(Perfect for your PayPal-style background)

Build:

• Tickets received

• SLA achieved

• Exceptions by region

• Ageing buckets

• Productivity

• Escalations

Lesson 11: Power BI Service

Learn:

• Publish reports

• Workspaces

• Apps

• Row Level Security

• Scheduled Refresh

Lesson 12: Performance Optimization

Use:

• Star Schema

• Measures instead of calculated columns

• Aggregation tables

• Query folding

Portfolio Projects (For Resume)

Project 1

Sales Dashboard

Skills:

• DAX

• Power Query

• Visualizations

Project 2

HR Analytics Dashboard

Metrics:

• Attrition

• Headcount

• Diversity

• Absenteeism

Project 3

Financial Dashboard

Metrics:

• Revenue

• Profit

• Budget vs Actual

Project 4 (Recommended for You)

Operations Excellence Dashboard

Based on your current work experience:

Metrics:

• SLA %

• Transaction Volume

• Exceptions

• Aging Cases

• Region Analysis

• Automation Savings

• Team Productivity

This project would look excellent on your resume for Analyst, Supervisor, or Team Leader roles.

Expert-Level Interview Questions

What is a Star Schema?

Difference between Measure and Calculated Column?

CALCULATE vs FILTER?

SUM vs SUMX?

Row Context vs Filter Context?

Import vs Direct Query?

Relationship Cardinality?

Master these and you'll perform well in most Power BI interviews.

Your First Homework

1. Install Power BI Desktop.

2. Import any Excel file.

3. Create:

   • Card visual

   • Bar chart

   • Pie chart

   • Slicer

4. Create this DAX measure:

DAX

Total Sales =

SUM(Sales[Amount])

5. Send me a screenshot or describe what you created, and I'll act as your personal Power BI coach and give you Lesson 2 with a real analyst project aligned to your Operations and Exceptions Management experience.