Forum Discussion

jpt1228's avatar
jpt1228
Responsive Resident
6 years ago
Solved

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 ...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    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 )
    )

     

lc_finance's avatar
lc_finance
Solution Sage
6 years ago

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