Forum Discussion
Last Price Paid Formula
Hello, I have a Dimdate table, item fact table. I want to create a report that will show the last price paid for an item regardless of time. The top data shows the date and purchase price by item. I want to summarize the items by the last price paid as in below the time series example. I tried to filter on max date but not getting expected result.
Thanks
Hi jpt1228 ,
Please check the following steps as below.
1. Unpivot the fact table as below to get the tranformed table, then close and aplly.
2. New a measure as below to get the excepted result.
Measure = VAR maxdate = CALCULATE ( MAX ( 'Table'[Attribute] ), ALLEXCEPT ( 'Table', 'Table'[Item] ) ) RETURN CALCULATE ( MAX ( 'Table'[Value] ), FILTER ( 'Table', 'Table'[Attribute] = maxdate ) )
2 Replies
Hi jpt1228 ,
You can do this in 2 steps:
1) use the function MAXX to find the last date for each item
2) use the function LOOKUPVALUE to find the price corresponding to the last date
This should give you what you want.
Let me know if you need any additional help,
LC
Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com
Hi jpt1228 ,
Please check the following steps as below.
1. Unpivot the fact table as below to get the tranformed table, then close and aplly.
2. New a measure as below to get the excepted result.
Measure = VAR maxdate = CALCULATE ( MAX ( 'Table'[Attribute] ), ALLEXCEPT ( 'Table', 'Table'[Item] ) ) RETURN CALCULATE ( MAX ( 'Table'[Value] ), FILTER ( 'Table', 'Table'[Attribute] = maxdate ) )
