Power BI DAX: CALCULATE, DATESBETWEEN, STARTOFYEAR, PREVIOUSYEAR, LASTDATE, SAMEPERIODLASTYEAR

Sales PYTD =
VAR StartYear =
    STARTOFYEAR ( PREVIOUSYEAR ( 'Calendar'[Date] ) )
VAR EndDate =
    LASTDATE ( SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )
RETURN
    CALCULATE ( [Sales], DATESBETWEEN ( 'Calendar'[Date], StartYear, EndDate ) )

Using the CALCULATE function with PREVIOUSYEAR and SAMEPERIODLASTYEAR in Power BI allows for precise year-over-year comparisons and trend analysis. This approach helps in dynamically calculating sales for the same period in the previous year, providing valuable insights into performance trends. By leveraging these functions, you can create robust and accurate time intelligence calculations that enhance your data analysis and reporting capabilities.

Scenario

You want to calculate the sales for the same period last year to compare it with the current year's sales.

Sample Data

Let's assume you have the following sales data:

DAX Expression

Here's how you can write the DAX expression to calculate the sales for the same period last year:

Sales PYTD =
VAR StartYear =
    STARTOFYEAR ( PREVIOUSYEAR ( 'Calendar'[Date] ) )
VAR EndDate =
    LASTDATE ( SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )
RETURN
    CALCULATE ( [Sales], DATESBETWEEN ( 'Calendar'[Date], StartYear, EndDate ) )

Explanation

1. StartYear: This variable captures the start of the year for the same period last year.

2. EndDate: This variable captures the last date of the same period last year. 3.

3. CALCULATE: This function calculates the total sales between the StartYear and EndDate.

Result

Using the sample data, the expression will calculate the sales for the same period last year (2022-01-01 to 2022-01-03) and compare it with the current year's sales (2023-01-01 to 2023-01-03).

This approach helps you dynamically calculate and compare sales year-over-year, providing valuable insights into performance trends.