I made a simple version of the file as an example.: Sample price indexation.pbix
Annually on 1 January, the prices of around 1,000 products are indexed. The prices are now all in separate Excel tables. I expand those each year with an extra line. In it, the previous year's price is increased by the indexation percentage retrieved from a table via a formula. To my mind, this is still time-consuming and above all error-prone. Now I'm brainstorming a solution using Power BI, but doubt it's the best solution (it is data creating, not analyzing). My idea is in the attached example.
- I take 2023 as the base year with index 100. There are different categories of products, each of which has its own index percentage. Each category has a separate column (Cat. 1 , Cat. 2 , Cat. 3).
- In the table Products there is a column Index which says which index Category should be used.
- The base premium of all products of Index 100 is in TablePremiumsIndex100
- The intention is to be able to add new products to the underlying Excel list and annually expand the Index table with a line for the new year.
- It would be even nicer if it is possible for a product that exists only from 1 January 2024 to be set to the 2024 price in the base price table because the start date of the product in the Table products is considered for the index to be determined.
Can you help think of the appropriate DAX formula for this?
