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:

Date	Sales
2023-01-01	100
2023-01-02	150
2023-01-03	200
2022-01-01	80
2022-01-02	120
2022-01-03	160

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

StartYear: This variable captures the start of the year for the same period last year.
EndDate: This variable captures the last date of the same period last year. 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.