How do you calculate prior year values?

[analyst31233]

How can I calculate prior year (PY) sales in Power BI for a matrix table, where the Month column (from the SalesOrderHeader table) is on the columns and Total Sales is the value? For example, in AdventureWorks, I have the Month (from OrderDate) in columns and SUM(SalesAmount) as values, but when I tried calculating PY sales using a formula like IF(Month = Month - 12, SalesAmount), the result was much higher than expected and didn’t match actual prior year sales. Also, I have SalesTerritory (or any categorical column) on rows. Any suggestions for calculating PY sales correctly?

Can anyone provide example of a working calc/measure in DAX for month-12 sales or year over year? 

[SacheeTh · Accepted solution]

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.

[v-pnaroju-msft]

Hi analyst31233,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Best regards,

Pavan

[v-pnaroju-msft · Accepted solution]

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

[SacheeTh · Accepted solution]

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.

[Data_Analyst905]

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. 

[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! 🚀

[Arul]

@analyst31233 ,

To calculate the prior year (PY) sales in Power BI, you need to create a measure that compares the sales amount of the current period with the sales amount from the same period in the previous year. The measure you attempted to use, IF(Month = Month - 12, SalesAmount), is not a valid DAX expression and will not give you the correct results.

Use the below measures,

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

OR

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

[analyst31233]

Hi @Arul . This reply was very helpful. Neither of these worked in my case and I'm not exactly sure why. I do have a calendar date table. I have it mapped from calendar date in the date table to The month ending date in the main table because I do not have every single day of the year in my main table, just the end of the month. For some reason both of those calculations you supplied don't result in any values or results, it's just completely blank. Any ideas why that could be? 

The calendar date table is mapped to the month end date many to one, with single direction and is marked as active. I'm a little bit puzzled

[v-pnaroju-msft]

Hi analyst31233,

Time intelligence functions like SAMEPERIODLASTYEAR and DATEADD assume a continuous date column in the date table and a valid relationship with the fact table. If the main table contains only month-end dates and not continuous daily dates, these functions may not behave as expected, as they rely on the full range of dates for offsets such as -1 YEAR.

Please follow the steps below to resolve the issue:

1. Ensure that the Date table contains continuous daily dates, even if the main table only includes month-end dates.

2. Mark the Date table as a Date Table in Power BI (via the "Mark as Date Table" option).

3. Maintain an active relationship between the Date table and the fact table through the month-end date.

4. Since the fact table contains only month-end dates, the calculation must account for this explicitly. Kindly find the updated measure below:

   PY Sales =
   CALCULATE(
   SUM(SalesTable[SalesAmount]),
   FILTER(
   ALL('Date'),
   'Date'[MonthEndDate] =
   EDATE(SELECTEDVALUE('Date'[MonthEndDate]), -12)
   )
   )

5. Verify that the month-end dates in the calendar Date table match exactly with the month-end dates in the fact table. Any mismatch (e.g., formatting differences) may cause the relationships or calculations to fail.

6. If values still appear blank:

   • Check whether the SalesTable[SalesAmount] column contains valid data.
   • Ensure that no filters in the report exclude relevant dates or sales data.
   • Confirm that the column used in the relationship (MonthEndDate) is correctly populated and matches the calendar table.

If you find this response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other members facing similar queries.

Best Regards,
Pavan