Excel Measure - Latest price applicable
Hi,
For the last 2 days, I have tried to find the measure I should use for this problem (without success obviously 🙂
So I have a table with 3 colums: Article, Price and Date_of_change. So the table contains the change of prices of articles during the year 2024. Sometimes for 1 article there is no change of price for several months: the datas will be empty for these months.
| Article | Date | Price |
| A | 01-01-24 | 100 |
| A | 01-02-24 | 105 |
| A | 01-04-24 | 115 |
| B | 01-01-24 | 150 |
| B | 01-03-24 | 155 |
| B | 01-04-24 | 165 |
| C | 01-02-24 | 205 |
| C | 01-04-24 | 215 |
| D | 01-03-24 | 100 |
I would like to create a measure allowing me to show in a pivot table the last price applicable for every end of month.
The results should be:
| Article | 31-01-24 | 29-02-24 | 31-03-24 | 30-04-24 |
| A | 100 | 105 | 105 | 115 |
| B | 150 | 150 | 155 | 165 |
| C | 205 | 205 | 215 | |
| D | 100 | 100 |
But I always retrieve the following result
| Article | 31-01-24 | 29-02-24 | 31-03-24 | 30-04-24 |
| A | 100 | 105 | 115 | |
| B | 150 | 155 | 165 | |
| C | 205 | 215 | ||
| D | 100 |
It looks like the relationship is not correct. Not sure about it.
I create in powerpivot a data_table from 1st jan 24 to 31st Dec (all dates) and add a field End_of_month for my pivot table. I create a relation between the Field Date (all dates) and the date from the Price_db
Could you please help me? What is the measure or/and the relationship that I should use?
Thanks a lot
Séb
Excel Measure - Latest price applicable