Sameperiodlastyear in DAX Power bi

In DAX, the SAMEPERIODLASTYEAR function is used to compare values from the same period in the previous year. This is particularly useful for year-over-year (YoY) analysis, allowing you to compare, for instance, the sales of May 2024 with the sales of May 2023.

Syntax

DAX

SAMEPERIODLASTYEAR(<dates>)

• <dates>: A column containing dates.

Implementation Example

Let’s implement a measure using the SAMEPERIODLASTYEAR function to compare the sales from the same period last year based on the sample data provided.

Steps

1. Ensure Date Table: Make sure there is a date table in your data model. This table should have a continuous range of dates covering all periods in your sales data.
2. Establish Relationships: Ensure the OrderDate column in the Sales table is related to the Date column in the Date table.
3. Create Measures: Define a measure to calculate total sales and another to calculate sales for the same period last year.

Sample Date Table

Here’s a simple Date table to use:

DAX

DateTable = CALENDAR(DATE(2023, 1, 1), DATE(2024, 12, 31))

Create Measures

1. Total Sales Measure:

DAX

Total Sales = SUM(Sales[SalesAmount])

2. Sales Same Period Last Year Measure:

DAX

Sales Same Period Last Year = 
CALCULATE(
    [Total Sales], 
    SAMEPERIODLASTYEAR('DateTable'[Date])
)

Example Usage in a Power BI Report

Let’s use the sample data provided and see how these measures would work.

Sample Data in Power BI

OrderID	OrderDate	SalesAmount	Category
1	2023-01-15	150.00	Technology
2	2023-02-20	250.00	Office Supplies
3	2023-03-25	300.00	Furniture
4	2023-04-10	100.00	Technology
5	2023-05-05	400.00	Office Supplies
6	2023-06-15	350.00	Furniture
7	2023-07-20	450.00	Technology
8	2023-08-25	200.00	Office Supplies
9	2023-09-10	500.00	Furniture
10	2023-10-15	600.00	Technology
11	2024-01-10	700.00	Office Supplies
12	2024-02-15	800.00	Furniture
13	2024-03-20	900.00	Technology
14	2024-04-25	1000.00	Office Supplies
15	2024-05-01	1100.00	Furniture
16	2024-05-05	1200.00	Technology
17	2024-05-10	1300.00	Office Supplies
18	2024-05-15	1400.00	Furniture
19	2024-05-20	1500.00	Technology
20	2024-06-01	1600.00	Office Supplies
21	2024-06-05	1700.00	Furniture
22	2024-07-10	1800.00	Technology
23	2024-08-15	1900.00	Office Supplies
24	2024-09-20	2000.00	Furniture
25	2024-10-25	2100.00	Technology
26	2024-11-01	2200.00	Office Supplies
27	2024-12-05	2300.00	Furniture
28	2024-12-10	2400.00	Technology
29	2024-12-15	2500.00	Office Supplies
30	2024-12-20	2600.00	Furniture

Result Analysis

1. Total Sales: This measure will simply sum up the SalesAmount for the selected period.
2. Sales Same Period Last Year: This measure will sum up the SalesAmount for the same period in the previous year.

For example, if you are looking at sales in May 2024:

• Total Sales for May 2024: The sum of SalesAmount for orders in May 2024.
  • 1100 + 1200 + 1300 + 1400 + 1500 = 6500
• Sales Same Period Last Year for May 2023: The sum of SalesAmount for orders in May 2023.
  • 400

By placing these measures in a visual, such as a table or line chart, you can easily compare the sales of May 2024 with the sales of May 2023.

Visual Example in Power BI

You can create a table or a line chart in Power BI to visualize this comparison. For instance:

• Table Visualization:
  • Columns: Date, Total Sales, Sales Same Period Last Year
  • Rows: May 2024
• Line Chart:
  • X-Axis: Date
  • Y-Axis: Total Sales, Sales Same Period Last Year

This will help you to visually compare the performance of sales between the current period and the same period last year.