Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |