First, you need to transform both columns year and its values in 2 columns only.
After that you can use:
PriceYear = 2016
PriceValue2016 * RELATED ( Table#1[Q-ty])
You will can use Treatas pattern. It provides virtual relationship between tables.
Try do this:
In your scenario, please open Query Editor, rename columns in second table to 2015, 2016, 2017, then select all these three columns, click Unpivot Columns button, apply the changes.
Then create a measure:
(LOOKUPVALUE(Table2[Value],'Table2'[Year],varMAX,'Table2'[Id_product],MAX('Table1'[Id_product])) - LOOKUPVALUE(Table2[Value],'Table2'[Year],varMin,'Table2'[Id_product],MAX('Table1'[Id_product])))*MAX('Table1'[Q-ty])
For details, you can download attached pbix file to have a look.
In you recommended case scenario after unpivoted colums I cann't create relationship beetween two table.
Because column Id_product in Table 1 and after unpivoted column Id_product in Table 2 will not unique .
In Table 1 primary key is id_transaction.
Have your new ideа?
Please review my previous post, I have created a pbix file based on the sample data in your original post.
What's the problem in your scenario now? I didn't see the id_transaction in your table data, please clarify sample data and your requirement.
It’s time for another PBI Community recap!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.
Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!