Power Pivot DAX PREVIOUSYEAR Function Challenge

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.