Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
https://msdn.microsoft.com/en-us/library/ee634806.aspx try this
Proud to be a Super User!
I can't ge the FIRSTDATE to work, maybe it needs to be combined with another function?
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
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |