Why time-based analysis drives decisions in Power BI - Business Case Study

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to understand how sales have changed over the past six months to make better inventory and marketing decisions.

📊 Data: You have monthly sales data including Date, Product Category, Region, and Sales Amount.

🎯 Deliverable: Create a Power BI report showing monthly sales trends with a line chart and a calculated measure for total sales per month.

Sample Data

Date	Product Category	Region	Sales Amount
2023-11-01	Electronics	North	12000
2023-11-01	Clothing	South	8000
2023-12-01	Electronics	North	15000
2023-12-01	Clothing	South	7000
2024-01-01	Electronics	North	13000
2024-01-01	Clothing	South	9000
2024-02-01	Electronics	North	16000
2024-02-01	Clothing	South	8500
2024-03-01	Electronics	North	17000
2024-03-01	Clothing	South	9500
2024-04-01	Electronics	North	18000
2024-04-01	Clothing	South	10000

1

Step 1: Load the sales data into Power BI Desktop by importing the table.

Use 'Get Data' > 'Excel' or 'CSV' depending on your file format, then load the data.

Expected Result

Sales data table appears in Power BI with columns Date, Product Category, Region, Sales Amount.

2

Step 2: Create a new calculated measure to sum sales amount for each month.

Total Sales = SUM('Sales'[Sales Amount])

Expected Result

A measure named 'Total Sales' that sums all sales amounts.

3

Step 3: Add a line chart visual to the report canvas.

Drag 'Date' to the X-axis and 'Total Sales' measure to the Y-axis.

Expected Result

Line chart shows total sales trend over the months from November 2023 to April 2024.

4

Step 4: Format the Date axis to show month and year clearly.

In the visual's X-axis settings, set type to 'Categorical' and format date as 'MMM yyyy'.

Expected Result

X-axis labels read as Nov 2023, Dec 2023, Jan 2024, etc.

5

Step 5: Add a slicer for Product Category to filter the sales trend by category.

Add slicer visual, drag 'Product Category' field into it.

Expected Result

User can select Electronics or Clothing to see sales trends for that category.

6

Step 6: Add a slicer for Region to filter sales by region.

Add slicer visual, drag 'Region' field into it.

Expected Result

User can select North or South region to filter the sales trend accordingly.

Final Result

Line chart showing upward trend

↓

Product Category slicer: Electronics | Clothing

↓

Region slicer: North | South

✓Sales have generally increased month over month from November 2023 to April 2024.

✓Electronics sales are higher than Clothing sales in all months.

✓North region sales are consistently higher than South region sales.

✓Time-based analysis helps identify growth trends and supports inventory planning.

Bonus Challenge

Create a measure to calculate the percentage growth in sales compared to the previous month and add it as a tooltip on the line chart.

Show Hint

Use the DAX function: Sales Growth % = DIVIDE([Total Sales] - CALCULATE([Total Sales], PREVIOUSMONTH('Sales'[Date])), CALCULATE([Total Sales], PREVIOUSMONTH('Sales'[Date])))