Why advanced DAX handles complex scenarios in Power BI - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants a report that shows monthly sales performance, including comparisons to previous months and year-to-date totals, with the ability to filter by product category and region.

📊 Data: You have sales transaction data with columns: Date, Region, Product Category, Sales Amount.

🎯 Deliverable: Create a Power BI report with measures using advanced DAX to calculate monthly sales, previous month sales, and year-to-date sales, all responsive to filters.

Sample Data

Date	Region	Product Category	Sales Amount
2024-01-15	North	Electronics	1200
2024-01-20	South	Clothing	800
2024-02-10	North	Electronics	1500
2024-02-15	East	Clothing	700
2024-03-05	South	Electronics	1300
2024-03-20	East	Clothing	900
2024-04-10	North	Clothing	1100
2024-04-15	South	Electronics	1400

1

Step 1: Create a measure to calculate total sales amount.

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

Expected Result

Total Sales sums all sales amounts in the filtered context.

2

Step 2: Create a measure to calculate sales for the previous month using DAX time intelligence.

Previous Month Sales = CALCULATE([Total Sales], PREVIOUSMONTH('Sales'[Date]))

Expected Result

Shows total sales for the month before the current filter context month.

3

Step 3: Create a measure to calculate year-to-date sales.

YTD Sales = TOTALYTD([Total Sales], 'Sales'[Date])

Expected Result

Shows total sales from the start of the year up to the current filter context date.

4

Step 4: Add slicers for Region and Product Category to the report to allow filtering.

Use Power BI slicer visual with fields 'Region' and 'Product Category'.

Expected Result

Report filters sales data dynamically by selected region and product category.

5

Step 5: Create a line chart visual with Date (month) on the axis and Total Sales, Previous Month Sales, and YTD Sales as values.

Configure line chart with 'Date'[Month] on X-axis and the three measures as lines.

Expected Result

Visual shows monthly sales trends, previous month comparison, and cumulative sales.

Final Result

Monthly Sales Report

Date       | Total Sales | Previous Month Sales | YTD Sales
------------------------------------------------------------
Jan 2024   | 2000        | -                   | 2000
Feb 2024   | 2200        | 2000                | 4200
Mar 2024   | 2200        | 2200                | 6400
Apr 2024   | 2500        | 2200                | 8900

(Line chart shows three lines tracking these values over months)

✓Sales increased steadily from January to April 2024.

✓Previous month sales measure helps compare month-over-month performance.

✓Year-to-date sales measure shows cumulative growth over the year.

✓Filtering by region and product category updates all measures dynamically.

Bonus Challenge

Create a measure that calculates the percentage change in sales compared to the previous month.

Show Hint

Use DIVIDE function with (Total Sales - Previous Month Sales) over Previous Month Sales to avoid division errors.