Forum Discussion

analyst31233's avatar
analyst31233
Regular Visitor
1 year ago
Solved

How do you calculate prior year values?

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 Adve...
Need Help
  • SacheeTh's avatar
    SacheeTh
    1 year ago

    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's avatar
    v-pnaroju-msft
    1 year ago

    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

Arul's avatar
Arul
Super User
1 year ago

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's avatar
    analyst31233
    Regular Visitor
    1 year ago

    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's avatar
      v-pnaroju-msft
      Community Support
      1 year ago

      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