Forum Discussion
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
Resolver IIHi 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.
- SacheeTh
Steps to Visualize in a Matrix Table:
Add Date hierarchy to Columns: For Month-level analysis, use the Month from your Date Table.
Add SalesTerritory (or any categorical column) to Rows.
Add both Total Sales and PY Sales measures to Values.Optional: Year-Over-Year (YoY) Calculation
To calculate YoY growth:
YoY Growth = DIVIDE( [Total Sales] - [PY Sales], [PY Sales], 0 )
This gives you the growth percentage compared to the prior year.Common Issues to Check:
Make sure your Date Table is set up correctly and linked to your fact table.
Verify that the Month in the matrix visual comes from the Date Table, not the SalesOrderHeader table.
Let me know if you need further clarification or help setting up the Date Table! 🚀 
Is it typical to have a date table in Power BI that is then related to your current data? I have traditionally brought in the data fully transformed thru SQL, and ready to work with in Power BI, but sometimes it is not feasible due to the size or scale.