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, "M...
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

     



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.