Show last values in in Monthly Matrix

[Arydperez]

I need to show in a Matrix the valid prices we have bought each product. Some monthes we didn't buy so the price should be the same of the previous month. If the product was bought twice in a month, it should show the last purchase (in the example Product B, month December).

I guess that I need to create a measure to show the same price of the last purchase but I don't know how to do it.

I am attaching a sample in order to show the example (and hopefully get a solution) 

https://drive.google.com/file/d/1TuTo7KSvAMuUpp98JOfxpeBE0OV1p4-j/view?usp=share_link

[Anonymous · Accepted solution]

Hello the sample data needs access so i created the same sample you provided:

Please follow carefully:
step 1: Go to power query
step 2: Add column End date and type in your end date for this example = "12/12/2022"
step 3: make sure the dates are in date type
step 4: add new column newDate = {Number.From([Date])..Number.From([End Date])}
step 5 : expand to new rows
step 6(optional) : delete end date column 
step 7: load your data
step 8: create a summarzied table of the table with the follwoing formulas:

New table = SUMMARIZE(Arydperez,Arydperez[Product],Arydperez[Custom],"Price",CALCULATE(MAX(Arydperez[Price]),Arydperez[Date] = MAX(Arydperez[Date])))

step 9: add month name column and Month column :

Month Name = FORMAT('New table'[newDate],"MMM")

Month = MONTH('New table'[newDate])

Step 10: Sort month name column by month column

Step 11: Create the measure:

Price Of = Price Of = CALCULATE(MAX('New table'[Price]),FILTER('New table','New table'[newDate] = MAX('New table'[newDate])))

step 12: Build your matrix

 

You can refer to the pwoerbi table and excel file below:
PBIX file: https://1drv.ms/u/s!Ag9tIyk2ofNRjmF6Fod9QpmJhYoz?e=IZ96zt
Excel file: https://1drv.ms/x/s!Ag9tIyk2ofNRjmLgSHLxrRONCu7p?e=hhLHbS

If you found the solution helpful, kindly accept as solution and a kudos is appreciated