Power Pivot DAX PREVIOUSYEAR Function Challenge

[jasonminhas]

XLSX Download link with Power Pivot model: Financial_Model_V17.9_Redacted.xlsx

Background
I'd like to create a measure that finds the values for the previous year on a quarter level. Using the PREVIOUSYEAR function is pretty straightforward in itself but the challenge is that it also row/column context from a field that comes from another table. I have redacted this file but have included notes in the first tab for context if it helps.

Objective
In the "Pivot Table" sheet you will see the measure "M_A_Last_Year_Qtr_Customers (Using Column)" (highlighted green). This measure uses a calculated column and shows the correct values because it takes into account the T-shirt Size field that resides in another table. The measure below it "M_A_Last_Year_Qtr_Customers (Using Measure)" is incorrect and I need it the have the same values as the other measure without using the calculated column.

Restrictions
The Pivot table format and dimension fields (Year, quarter, T-shirt Size) must stay the same and come from the current tables. For example, I can't change the T-shirt Size field to come from the same table the measure resides in because I will eventually expand this model to include other tables that will also use the T-shirt_Size_Helper table and I need the relationships to stay as is.

[123abc]

To create a measure that calculates the values for the previous year on a quarter level and takes into account the T-shirt Size field that resides in another table, you can use DAX in Power Pivot. You can achieve this by using the PREVIOUSYEAR function and FILTER function. Here's how you can do it without using a calculated column:

M_A_Last_Year_Qtr_Customers (Using Measure) =
VAR CurrentYear = MAX('Pivot Table'[Year])
VAR PreviousYear = CurrentYear - 1
RETURN
SUMX(
FILTER(
ALL('Pivot Table'),
'Pivot Table'[Year] = PreviousYear
),
CALCULATE(
[Total Customers], -- Assuming this is the measure for the current year's customers
'T-shirt_Size_Helper'[T-shirt Size] IN VALUES('Pivot Table'[T-shirt Size])
)
)

In this formula, we first define two variables to capture the current year and the previous year. Then, we use the FILTER function to filter the data from the 'Pivot Table' for the previous year. Finally, we use SUMX to sum up the customers for the filtered data, but we use CALCULATE to consider the T-shirt Size from the 'T-shirt_Size_Helper' table using the IN operator and VALUES function.

This measure should produce the same results as the measure that uses the calculated column but without the need for a calculated column. It respects the relationship between the tables and the existing structure of your Pivot Table.

Make sure to replace [Total Customers] with the actual measure for the current year's customers in your model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[jasonminhas]

Hey 123abc,

Thanks for your help! I'm using the DAX formula below but I'm getting an error in a couple of spots.

M_A_Last_Year_Qtr_Customers (Using Measure):=

VAR CurrentYear = MAX('Pivot Table'[Year])

VAR PreviousYear = CurrentYear - 1

RETURN

SUMX(

FILTER(

ALL('Pivot Table'),

'Pivot Table'[Year] = PreviousYear

),

CALCULATE(

[M_A_Yearly_Customers], -- Assuming this is the measure for the current year's customers

'T-shirt_Size_Helper'[T-shirt Size] IN VALUES('Pivot Table'[T-shirt Size])

)

)

I have follow-up questions:

1. I changed [Total Customers] to [M_A_Yearly_Customers] but am I supposed to replace 'Pivot Table' name with 'Scn_A_Target1_Customers' which is the name the measure resides in? The DAX formula is not recognizing 'Pivot Table' because I don't have a table called 'Pivot Table'.
2. I understand the purpose of IN VALUES but DAX is also indicating an error for this within Power Pivot