cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

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.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors