Forum Discussion

mihaigm's avatar
mihaigm
Helper I
1 year ago
Solved

Calculated column with latest value from another table

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!!! 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     



4 Replies

  • mihaigm's avatar
    mihaigm
    Helper I
    1 year ago

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

     

    Thank you again...

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      mihaigm
      Helper I
      1 year ago

      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!!