JasonMinhas
New Member
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.
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.
Attachments
Last edited: