I have a Power Pivot made up of data from 8 tables. One table is 2022 Sales, one table is 2023 Sales. The line items are assigned a customer, brand, and month. I have created a 1:Many relationship for customer name, brand, and month for both the 2022 sales and the 2023 sales tables. Now I need to calculate the % growth between the line items.
For example, I need to see the increase in sales for Customer A purchasing Brand X during the month of March 2022 vs March 2023. I tried to add a calculated column in the 2023 sales table using the formula
=('2023 Sales'[Sales]-RELATED('2022 Sales'[Sales]))/RELATED('2022 Sales'[Sales])
I receive the following error: The column '2022 Sales[Sales]' either doesn't exist or doesn't have a relationship to any table available in the current context.
This doesn't make sense to me because I specifically built relationships from both the 2023 Sales table and the 2022 Sales table to my reference tables of Customer, Brand, and Month. Both sales tables are linked to the same reference tables.
What am I doing wrong? Is it possible for Power Pivot to identify a matching line item based on 3 criteria (customer, brand, month)?
