How do you calculate prior year values?

Hi analyst31233,

To calculate prior year (PY) sales, can use the DAX functions like the SAMEPERIODLASTYEAR function or the DATEADD functions, instead of manual logic like "Month = Month - 12," which does not account for proper date relationships and aggregations. Assuming you have a date table (highly recommended) that is marked as a "Date Table" in Power BI and properly linked to your sales data, the measure for PY sales can be written as follows:

For SAMEPERIODLASTYEAR:

PY Sales = 
 CALCULATE(
  SUM(SalesOrderHeader[SalesAmount]),
  SAMEPERIODLASTYEAR('Date'[Date])
 )

Alternatively, using DATEADD:

PY Sales = 
 CALCULATE(
  SUM(SalesOrderHeader[SalesAmount]),
  DATEADD('Date'[Date], -1, YEAR)
 )

These measures work dynamically because SAMEPERIODLASTYEAR and DATEADD adjust the context of the calculation to shift it by one year based on the current period (month, quarter, or year) in your matrix table. Make sure your 'Date' table has continuous dates, and that it is properly related to the OrderDate column in the SalesOrderHeader table. Also, ensure you are using 'Date' from the Date table for columns in the matrix, not directly from the Sales table. This approach ensures accurate prior year sales values that match expectations, even with filters like SalesTerritory or other categorical columns on rows.

Hi analyst31233,

We value your inquiry through the Microsoft Fabric Community Forum.

In response to your query, we have attached the relevant screenshot and the PBIX file to assist you in resolving the issue.

If you find the response helpful, we kindly request you to mark it as the accepted solution and provide kudos, as it may assist other members with similar queries.

Best regards,
Pavan