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
Arul's avatar
Arul
Icon for Super User rankSuper 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
    Icon for Community Support rankCommunity 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