cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Lookup text based on start and end date

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:

 

Capture.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Lookup text based on start and end date

@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]
    )
)

23.PNG

 

Regards,

 

 

View solution in original post

1 REPLY 1
Highlighted
Microsoft
Microsoft

Re: Lookup text based on start and end date

@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]
    )
)

23.PNG

 

Regards,

 

 

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors