SAMEPERIODLASTYEAR in Power BI - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the DAX function SAMEPERIODLASTYEAR do?

It returns a table of dates shifted one year back from the dates in the current context. This helps compare data from the same period last year.

beginner

Write a simple DAX measure using SAMEPERIODLASTYEAR to calculate last year's sales.

Last Year Sales = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date]))

intermediate

Why do you need a continuous date column when using SAMEPERIODLASTYEAR?

Because SAMEPERIODLASTYEAR works on a continuous date column to correctly shift dates by one year. Missing dates can cause wrong or empty results.

beginner

Can SAMEPERIODLASTYEAR be used with non-date columns?

No. SAMEPERIODLASTYEAR only works with date columns because it shifts dates by one year to find the matching period.

intermediate

What is the difference between SAMEPERIODLASTYEAR and PARALLELPERIOD?

SAMEPERIODLASTYEAR shifts the dates exactly one year back keeping the same period length, while PARALLELPERIOD can shift by months, quarters, or years and allows more flexible offsets.

What does SAMEPERIODLASTYEAR return?

A A table of dates shifted one year back

B Sum of sales from last year

C Current year dates

D A single date value

SAMEPERIODLASTYEAR returns a table of dates shifted exactly one year back from the current context.

Which column type is required for SAMEPERIODLASTYEAR to work correctly?

A Continuous date column

B Boolean column

C Numeric column

D Text column

SAMEPERIODLASTYEAR needs a continuous date column to shift dates by one year properly.

Which DAX function can be combined with SAMEPERIODLASTYEAR to calculate last year's sales?

A SUMX

B FILTER

C CALCULATE

D VALUES

CALCULATE changes the filter context and is used with SAMEPERIODLASTYEAR to calculate last year's sales.

Can SAMEPERIODLASTYEAR shift dates by months or quarters?

A Yes, it can shift by months

B No, only by one year

C Yes, by quarters

D Yes, by days

SAMEPERIODLASTYEAR only shifts dates exactly one year back, not by months or quarters.

What happens if the date column has missing dates when using SAMEPERIODLASTYEAR?

A It still works perfectly

B It fills missing dates automatically

C It returns an error

D It may return incorrect or empty results

Missing dates can cause SAMEPERIODLASTYEAR to return wrong or empty results because it expects continuous dates.

Explain how SAMEPERIODLASTYEAR helps in comparing sales data year over year.

Think about how you compare this year's sales to last year's same time.

Describe the correct way to write a DAX measure using SAMEPERIODLASTYEAR to calculate last year's total sales.

Combine CALCULATE and SAMEPERIODLASTYEAR with SUM.

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