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

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
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 )
    )