Calculate Value of First Purchase per Customer

[Milan_TBC]

Hi all,

In order to make calculations regarding new business flow, i want to see the first purchase price (or deal_id) per customer in the customer data sheet. My sheets are connected as followed:

I want to fill the calculated column <First Deal> with the weigthed_value_amount out of the left data set. 
This can be done by finding the first closed_at in the left column. I already have this date in the <First Order Date> in the right data set, however i cannot get it to work with weighted_value_amount. 

I have tried LOOKUPVALUE(), and i have tried messing around with TOPN(), however i can not get it to work. 

Does someone know how to get this done?

Thanks in advance!

[Anonymous · Accepted solution]

Hi @Milan_TBC ,

Please try:

First Deal = 
VAR _min_date =
    CALCULATE ( MIN ( 'Table2'[First Order Date] ), RELATEDTABLE ( 'Table2' ) )
VAR _first_deal =
    CALCULATE (
        MAX ( 'Table2'[First Deal] ),
        FILTER (
            ALL ( 'Table2' ),
            'Table2'[id] = 'Table1'[lead customer id]
                && 'Table2'[First Order Date] = _min_date
        )
    )
RETURN
    _first_deal

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum