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 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

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

Upcoming Events

Upcoming Events

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

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors