SAMEPERIODLASTYEAR in Power BI - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to compare monthly sales this year with the same months last year to understand growth trends.

📊 Data: You have a sales dataset with columns: Date (daily), Sales Amount, and Product Category.

🎯 Deliverable: Create a report showing monthly sales for the current year and the same months last year side by side.

Sample Data

Date	Sales Amount	Product Category
2023-01-15	1000	Electronics
2023-01-20	1500	Furniture
2023-02-10	1200	Electronics
2023-02-25	1300	Furniture
2022-01-18	900	Electronics
2022-01-22	1100	Furniture
2022-02-12	1000	Electronics
2022-02-28	1200	Furniture
2023-03-05	1400	Electronics
2023-03-15	1600	Furniture
2022-03-10	1300	Electronics
2022-03-20	1500	Furniture

1

Step 1: Create a Date table with continuous dates covering all sales dates.

Use Power BI's 'New Table' feature with formula: Date = CALENDAR(DATE(2022,1,1), DATE(2023,12,31))

Expected Result

A Date table with all dates from Jan 1, 2022 to Dec 31, 2023.

2

Step 2: Mark the Date table as a Date table in Power BI.

Select the Date table, then 'Mark as Date Table' and choose the Date column.

Expected Result

Power BI recognizes the Date table for time intelligence functions.

3

Step 3: Create a relationship between the Sales data Date column and the Date table Date column.

In Model view, drag Sales[Date] to Date[Date] to create a one-to-many relationship.

Expected Result

Sales data is connected to the Date table for filtering.

4

Step 4: Create a measure for Total Sales.

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

Expected Result

A measure that sums all sales amounts.

5

Step 5: Create a measure for Sales Same Period Last Year using SAMEPERIODLASTYEAR function.

Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

Expected Result

A measure that calculates sales for the same period last year.

6

Step 6: Create a matrix visual with rows as Year and Month from the Date table.

Add 'Date'[Year] and 'Date'[Month] to Rows in the matrix visual.

Expected Result

Matrix shows Year and Month as row headers.

7

Step 7: Add the Total Sales and Sales LY measures to the Values area of the matrix.

Drag 'Total Sales' and 'Sales LY' measures into Values.

Expected Result

Matrix shows sales for current year and same period last year side by side.

8

Step 8: Format the matrix to show sales amounts as currency and sort by Year then Month.

Use visual formatting pane to set currency format and sort order.

Expected Result

Matrix is easy to read with properly formatted sales values.

Final Result

Monthly Sales Comparison Report

Year  Month   Total Sales   Sales LY
2022  January  2000         -
      February 2200         -
      March    2800         -
2023  January  2500         2000
      February 2500         2200
      March    3000         2800

✓Sales increased in January 2023 compared to January 2022 by 500.

✓February 2023 sales also grew by 300 compared to last year.

✓March 2023 sales showed an increase of 200 compared to March 2022.

Bonus Challenge

Create a line chart showing monthly sales trends for current year and last year on the same graph.

Show Hint

Use the Date table's Month column on the X-axis and add both 'Total Sales' and 'Sales LY' measures as lines.

Practice

1. What is the main purpose of the SAMEPERIODLASTYEAR function in Power BI?

easy

A. To calculate the total sales for the current year

B. To compare data from the same period in the previous year

C. To filter data for the current month only

D. To create a new date column with next year's dates

Solution

1. Step 1: Understand the function's purpose

   SAMEPERIODLASTYEAR shifts the date context back exactly one year to compare the same time periods.

2. Step 2: Identify the correct use case

   It is used to compare measures like sales or revenue from this year to the same period last year.

3. Final Answer:

   To compare data from the same period in the previous year -> Option B

4. Quick Check:

   SAMEPERIODLASTYEAR compares last year's period [OK]

Hint: Think "same time last year" for comparison [OK]

Common Mistakes:

• Confusing it with total aggregation functions
• Using it to filter current year data
• Assuming it creates future dates

2. Which of the following is the correct syntax to use SAMEPERIODLASTYEAR with a date column named Sales[Date]?

easy

A. SAMEPERIODLASTYEAR('Sales'[Date])

B. SAMEPERIODLASTYEAR(Sales[Date])

C. SAMEPERIODLASTYEAR(Sales.Date)

D. SAMEPERIODLASTYEAR([SalesDate])

Solution

1. Step 1: Recognize correct table and column reference

   In DAX, table and column names are referenced as 'TableName'[ColumnName], so 'Sales'[Date] is correct.

2. Step 2: Check syntax correctness

   SAMEPERIODLASTYEAR(Sales[Date]) omits single quotes around the table name. SAMEPERIODLASTYEAR(Sales.Date) uses invalid dot notation. SAMEPERIODLASTYEAR([SalesDate]) uses an incorrect column name format.

3. Final Answer:

   SAMEPERIODLASTYEAR('Sales'[Date]) -> Option A

4. Quick Check:

   Correct DAX column reference uses quotes and brackets [OK]

Hint: Use single quotes for table, brackets for column [OK]

Common Mistakes:

• Omitting single quotes around table name
• Using dot notation instead of brackets
• Referencing columns without table name

3. Given the measure:

Sales LY = CALCULATE(SUM('Sales'[Amount]), SAMEPERIODLASTYEAR('Sales'[Date]))

If total sales for March 2023 are 1000, and for March 2022 are 800, what will Sales LY show when filtered on March 2023?

medium

A. 800

B. 1800

C. 1000

D. 0

Solution

1. Step 1: Understand the measure calculation

   The measure sums sales amounts but shifts the date filter to the same period last year using SAMEPERIODLASTYEAR.

2. Step 2: Apply the filter context for March 2023

   When filtered on March 2023, the measure returns sales from March 2022, which is 800.

3. Final Answer:

   800 -> Option A

4. Quick Check:

   Sales LY shows last year's same period sales [OK]

Hint: SAMEPERIODLASTYEAR shifts filter to last year [OK]

Common Mistakes:

• Assuming it sums current year sales
• Adding current and last year sales
• Returning zero if no explicit filter

4. You wrote this measure:

Sales LY = CALCULATE(SUM('Sales'[Amount]), SAMEPERIODLASTYEAR(Sales[Date]))

But it returns an error. What is the likely cause?

medium

A. Measure name cannot contain spaces

B. SUM function cannot be used inside CALCULATE

C. SAMEPERIODLASTYEAR requires a numeric column

D. Missing single quotes around the table name 'Sales'

Solution

1. Step 1: Check table and column references

   The table name 'Sales' must be enclosed in single quotes when referenced in DAX, so Sales[Date] is invalid.

2. Step 2: Understand function requirements

   SUM inside CALCULATE is valid, SAMEPERIODLASTYEAR requires a date column, and measure names can have spaces.

3. Final Answer:

   Missing single quotes around the table name 'Sales' -> Option D

4. Quick Check:

   Table names need single quotes in DAX [OK]

Hint: Always quote table names in DAX formulas [OK]

Common Mistakes:

• Forgetting single quotes around table names
• Thinking SUM can't be inside CALCULATE
• Assuming SAMEPERIODLASTYEAR needs numeric columns

5. You want to create a measure that shows the percentage change in sales compared to the same period last year. Which DAX formula correctly uses SAMEPERIODLASTYEAR to calculate this?

hard

A. Sales YoY % = CALCULATE(SUM('Sales'[Amount]), SAMEPERIODLASTYEAR('Sales'[Date])) - SUM('Sales'[Amount])

B. Sales YoY % = SUM('Sales'[Amount]) / SAMEPERIODLASTYEAR('Sales'[Date])

C. Sales YoY % = DIVIDE(SUM('Sales'[Amount]) - CALCULATE(SUM('Sales'[Amount]), SAMEPERIODLASTYEAR('Sales'[Date])), CALCULATE(SUM('Sales'[Amount]), SAMEPERIODLASTYEAR('Sales'[Date])))

D. Sales YoY % = SUM('Sales'[Amount]) - CALCULATE(SUM('Sales'[Amount]), SAMEPERIODLASTYEAR('Sales'[Date]))

Solution

1. Step 1: Understand the percentage change formula

   Percentage change = (Current - Previous) / Previous, so we need current sales minus last year's sales divided by last year's sales.

2. Step 2: Analyze each option

   Sales YoY % = DIVIDE(SUM('Sales'[Amount]) - CALCULATE(SUM('Sales'[Amount]), SAMEPERIODLASTYEAR('Sales'[Date])), CALCULATE(SUM('Sales'[Amount]), SAMEPERIODLASTYEAR('Sales'[Date]))) correctly uses DIVIDE to avoid errors and calculates the difference over last year's sales. Sales YoY % = SUM('Sales'[Amount]) / SAMEPERIODLASTYEAR('Sales'[Date]) divides sales by a date column, which is invalid. Sales YoY % = CALCULATE(SUM('Sales'[Amount]), SAMEPERIODLASTYEAR('Sales'[Date])) - SUM('Sales'[Amount]) subtracts current from last year without division. Sales YoY % = SUM('Sales'[Amount]) - CALCULATE(SUM('Sales'[Amount]), SAMEPERIODLASTYEAR('Sales'[Date])) calculates difference but not percentage.

3. Final Answer:

   Sales YoY % = DIVIDE(SUM('Sales'[Amount]) - CALCULATE(SUM('Sales'[Amount]), SAMEPERIODLASTYEAR('Sales'[Date])), CALCULATE(SUM('Sales'[Amount]), SAMEPERIODLASTYEAR('Sales'[Date]))) -> Option C

4. Quick Check:

   Percentage change = (Current - LastYear) / LastYear [OK]

Hint: Use DIVIDE for safe percentage calculations [OK]

Common Mistakes:

• Dividing by a date column instead of sales
• Forgetting to divide difference by last year's sales
• Subtracting without calculating percentage