Update query - power query

[AliBb]

I have a table of customers with an allocated sales lead - I have a table of transactions for customers. I've the relationship set up in power pivot and the salesman flows through no problem.

The issue I have is when the allocated sales lead changes.

The pivots give amounts by salesman for each period / year. With the query always dynamically looking up the sales lead the historic data is always skewed by any changes to the salesforce as customers get allocated when salesmen leave.

At the point of running the power query I would like the data to be updated permanently for the transactions that don't yet have a salesman identified on them - is this possible?

 

[macfuller]

I'm not quite clear on your question as it appears your first question deals with how to handle historical data when the sales lead changes, but then you ask for data to be updated when there isn't a sales lead.

What is it that you'd like updated? If Bob had the Acme account until April when Alice took it over, do you want Bob to continue to get credit for historical Acme even though he now handles Spacely Sprockets or do you want Alice to get the historical credit?
For the pending transactions of Cogswell Cogs if they move to Ted when he gets the assignment will they "permanently" be Ted's, or will Carol get them sometime in the future if Ted gets assigned elsewhere?

Offhand it sounds like you need an "effective date" lookup table for when a sales rep owned an account.

 

[AliBb]

I would like the historical data to remain as is ie in the example you gave Bob would always have the credit for Acme and Alice would only have the credit when she took over.
The effective date lookup sounds like it may work. How would this be achieved using power query?

 

[macfuller]

The effective date data would have to be maintained somewhere it could subsequently be imported into the data model. Off the top of my head I'd say it's more of a DAX issue - you would want your total sales etc. measures to depend on the lookup of the effective date.

Without knowing how your data looks I can't be too specific. If you have a begin and end date for the dates that Bob had Acme that's different than if you have a single effective date for Bob and would have to check the account info for an effective date later than Bob's (e.g. Alice) to know the date range.

A quick Google search brings up this somewhat similar post on matching discount rates to customers for a certain time period. Or this one.

 

[AliBb]

Thank you. All working now as planned