Lookup text based on start and end date

[Tachyon75]

Hi

I'm attempting to create a column in my Sales table which will return the name of the sales rep in charge of the entity at that point in time(the result column in the example below) .

Sales reps are allocated based on the first letter of the account holders name (letter), the state of the account holder, and the brand associated with the account holder. This allocation changes periodically, with the Sales Rep Table storing these allocations along with a start date and an end date for that allocation (blank end date means that the allocation is current).

In the past I've had a static view of the sales rep table which just had the current allocation, and therefore I just created a concatenation of letter-state-brand in both tables and did a standard lookupvalue formula to add the name to the sales table. With the date dimension however this obviously doesn't work.

Any help would be greatly appreciated!

Thanks

Example data:

[v-sihou-msft · Accepted solution]

@Tachyon75

You can create a calculated column like below:

Result =
CALCULATE (
    VALUES ( 'Sales Rep'[Name] ),
    FILTER (
        'Sales Rep',
        'Sales Rep'[StartDate] <= Sales[Date]
            && OR ( 'Sales Rep'[EndDate] > Sales[Date], ISBLANK ( 'Sales Rep'[EndDate] ) )
            && 'Sales Rep'[Letter] = Sales[Letter]
            && Sales[Brand] = 'Sales Rep'[Brand]
            && Sales[State] = 'Sales Rep'[State]
    )
)

Regards,