Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
epod184
Frequent Visitor

Calculated Column

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_idsold_pricesale_datefirst_sale_price
B0119485.4731/07/20175.47
B0119485.4701/08/20175.47
B0119485.4902/08/20175.47
B0119485.4903/08/20175.47
B0119485.5204/08/20175.47
B1XG9G02.5902/08/20172.59
B1XG9G02.6003/08/20172.59
B1XG9G02.6104/08/20172.59
B8JYLC73.5703/08/20173.57
B8JYLC73.6504/08/20173.57

 

Please help!

thanks

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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

1.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-yulgu-msft
Employee
Employee

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

1.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-yulgu-msft

You're a legend... works perfectly 😄

vanessafvg
Super User
Super User

@epod184

 

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

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

Vvelarde
Community Champion
Community Champion

@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




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

Vvelarde
Community Champion
Community Champion

@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




Lima - Peru

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.