Calculated column with latest value from another table

[mihaigm]

Hello dear friends!

I'm working on a project in PowerBi and just to simplify the example, I'm having 2 tables, one for the sales and the other for the sales persons. In the "Sales Persons" table, "Mark" is the name assigned to an article "cell" since 01/01/2025. But on 14/01/2025 the seller that was assigned to "cell" article is Bob.

So, my question is: How can I assign the name of the right seller in my "Sales" tables based on the fact that in the "Sales person" table, previous of 14/01/2025 was "Mark" but after 14/01/2025 it is Bob? So on the first row of the "Sales" table I shoud have Mark, but on the third row I should have Bob as the new sales person... How can I create a calculated column??

I hope you understand my issue. I will leave the examples below:

Tab. "Sales":

Tab "Sales person":

Thank you so much for your help!!! 

[Anonymous · Accepted solution]

Hi @mihaigm ,
Thanks for danextian reply.
You can create two calculate columns to achieve this
In sales person table

NextDate = 
VAR CurrentCompany = [Company]
VAR CurrentArea = [Area]
VAR CurrentProd = [Prod]
VAR CurrentDate = [Date]
VAR NextDate = 
    MINX(
        FILTER(
            'Sales person',
            'Sales person'[Company] = CurrentCompany &&
            'Sales person'[Area]= CurrentArea &&
            'Sales person'[Prod] = CurrentProd &&
            'Sales person'[Date] > CurrentDate
        ),
        'Sales person'[Date]
    )
RETURN
IF(ISBLANK(NextDate), DATE(2025, 12, 31), NextDate)

In Sales table

Seller Name = 
VAR CurrentDate = 'Sales'[Date]
VAR CurrentProd = 'Sales'[Prod]
VAR CurrentArea = Sales[Area]
VAR CurrentCompany = 'Sales'[Company]
RETURN
    CALCULATE(
        MAX('Sales person'[Seller Name]),
        FILTER(
            'Sales person',
            'Sales person'[Prod] = CurrentProd &&
            'Sales person'[Company] = CurrentCompany &&
            'Sales person'[Area] = CurrentArea &&
            'Sales person'[Date] <= CurrentDate &&
            'Sales person'[NextDate] >= CurrentDate
        )
    )

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[mihaigm]

Hello @Anonymous and Thank you so much for your help!!! It works perfectly fine!!!! 😊😎Now I'm playing around with more complex formulas!!! Wish you a great evening!! and a great Week, too!! 

[danextian]

Hi @mihaigm 

Please try this:

CALCULATE (
    MAX ( 'sales person'[seller name] ),
    FILTER (
        'sales person',
        'sales person'[company] = EARLIER ( 'sales'[company] )
            && 'sales person'[area] = EARLIER ( 'sales'[area] )
            && EARLIER ( 'sales'[date] ) <= 'sales person'[date]
    )
)

Start and end date columns in the Sales Person table would make this more accurate.

[mihaigm]

 Of course, I need help with the DAX formula for the calculated column... 😊

Thank you again...