Calculated Column

[epod184]

Hi,

I'm looking to create a calculated column to lookup the first price an item was sold at.

I want the calculated column to = 'first_sale_price' in the example below.

So for each product_id, I want to return the sold_price from the earliest sale_date. This table will eventually have a report slicer on it, to filter the sale_date column, and I want the calculated column for first_sale_price to update accordingly.

product_id	sold_price	sale_date	first_sale_price
B011948	5.47	31/07/2017	5.47
B011948	5.47	01/08/2017	5.47
B011948	5.49	02/08/2017	5.47
B011948	5.49	03/08/2017	5.47
B011948	5.52	04/08/2017	5.47
B1XG9G0	2.59	02/08/2017	2.59
B1XG9G0	2.60	03/08/2017	2.59
B1XG9G0	2.61	04/08/2017	2.59
B8JYLC7	3.57	03/08/2017	3.57
B8JYLC7	3.65	04/08/2017	3.57

Please help!

thanks

[v-yulgu-msft · Accepted solution]

Hi @epod184,

Since calculated column is always static, if you want its values for first_sale_price to update according to the selected date range from slicer, you should create a measure.

FirstPrice measure =
VAR mindate =
    CALCULATE (
        MIN ( Table1[sale_date] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[product_id] = MAX ( Table1[product_id] )
        )
    )
RETURN
    CALCULATE (
        LASTNONBLANK ( Table1[sold_price], 1 ),
        FILTER (
            ALL ( Table1 ),
            Table1[sale_date] = mindate
                && Table1[product_id] = MAX ( Table1[product_id] )
        )
    )

Best regards,
Yuliana Gu

[epod184]

Thanks @v-yulgu-msft

You're a legend... works perfectly 😄

[vanessafvg]

@epod184

https://msdn.microsoft.com/en-us/library/ee634806.aspx try this

[epod184]

I can't ge the FIRSTDATE to work, maybe it needs to be combined with another function?

[Vvelarde]

@epod184

Hi, you can create a calculated column with this Dax:

FirstPrice =
CALCULATE (
    MIN ( Table1[sold_price] ),
    FILTER (
        ALL ( Table1 ),
        FIRSTDATE ( Table1[sale_date] )
            && Table1[product_id] = EARLIER ( Table1[product_id] )
    )
)

Regards

Victor

Lima - Peru

[epod184]

Hi @Vvelarde,

Thanks very much, for your reply.

I think this works on the assumption that the sold_price always increases, hence in that situation the MIN(table[sold_price]) works.

But sometimes the sold_price can decline over time, and this formula brings back the lowest... not the first sold

[Vvelarde]

@epod184

Hi don't have time to reduce in a better version but works.

FirstPrice =
VAR FirstSaleDate =
    CALCULATE (
        MIN ( Table1[sale_date] ),
        FILTER ( ALL ( Table1 ), Table1[product_id] = EARLIER ( Table1[product_id] ) )
    )
RETURN
    CALCULATE (
        MIN ( Table1[sold_price] ),
        FILTER (
            ALL ( Table1 ),
            Table1[sale_date] = FirstSaleDate
                && Table1[product_id] = EARLIER ( Table1[product_id] )
        )
    )

Regards

Victor

Lima - Peru