Splitting Revenue Based on Dates?

[Anonymous]

I will break this down as much and clearly as I possibly can. So currently, I have a dashboard built that looks at accounts, account managers, and the associated revenue. Looks like this:

 

There are also invoice dates for each row above, just not pictured.

Based on this information, I've built a formula that calculates the last 3 months of revenue and multiples it by 4 which works as intended (The [extended] column in the formula is actually the revenue):

As a result of this formula, I get this little table with the amounts for each account manager that shows the total amounts for all accounts they manage:

The new challenge:

Account managers can switch accounts at any given time. So let's say I made $50,000 on an account, and for whatever reason I had to give the account to another co worker. When the names update in the system, it would just look like whoever the new account manager is made the $50,000 even though technically it was me.

What I've been tasked with is ensuring that the $50,000 made by me is still credited to me, and all new revenue going forward is credited to the new account manager. So in our SQL database, they've made a few changes to show when an account is handed over to a new person:

As an example, above we can see Kathy was the AM from 12/11/2015 - 1/3/2017 then it was Sara. Then on 3/21/2017 it was then handed over to Latricia. So I know in a formula somewhere, I would need to incoporate the entrydate field above, but I'm just not sure how.

So the simplified verson is, the revenue for a given account needs to be split between the people who have owned it past or present based on when they owned the account. So if Kathy owned it from 12/11/2015 to 1/3/2017, all the invoice dates and their amounts within that date range would apply to her, etc. etc.

The idea is that in my [3 month revenue] formula above, an account could be switched within that 3 month period. We want to ensure the revenue is split accordingly between AM's if there was a switch.

[v-jiascu-msft]

Hi @Anonymous,

Is it like the demo in the attachment? @Anonymous's solution is a good idea. please refer to the demo. 

1. Create a column which indicates the end of the interval.

EndDate =
VAR temp =
    CALCULATE (
        MIN ( Table2[EntryDate] ),
        FILTER ( 'Table2', 'Table2'[EntryDate] > EARLIER ( 'Table2'[EntryDate] ) )
    )
RETURN
    IF ( ISBLANK ( temp ), DATE ( 9999, 12, 31 ), temp )

2. Create a column to split the entries to AMs.

ActualAM =
MINX (
    FILTER (
        'Table2',
        'Table2'[EntryDate] <= [Invoice Date]
            && Table2[EndDate] > [Invoice Date]
    ),
    'Table2'[AM]
)

Best Regards,
Dale

[Ashish_Mathur]

Hi,

Could you share a small dataset and also show the expected result.

[Anonymous]

Hi @Ashish_Mathur and @Anonymous, I will try my best to break this down more clearly. Difficult to explain so a closer look in detail:

Same account, owned by 3 different people at 3 different times. I only have an entry date to show when they took over the account, I do not have an end date in place, though I wish I did.

So in my billing table which is separate, that company looks like this. The extended column is the revenue:

 

Then if I go to my visual I see this (visual is a mix of Salesforce and SQL data):

All money on this account is under Latricia Collins because she is the latest owner of this account in Salesforce. What I'm aiming to do is only have the revenue calculated for the time they owned the account.

So let's say to date the total amount on the account regardless of account manager is $99,000. I would like to be able to click on this company and see:

 

So my guess is, there has to be some kind of relationship working between the entry date fields from table 1 and the invoice date field from table 2, but I just have no clue on what that would look like.

The trickier part is figuring out how to do all of that within a formula that also calculates the last 3 months of revenue for all accounts and multiplying by 4, and taking into account any account manager changes that may have taken place. Right now that formula looks like this:

So I'm not sure if i have to build an entirely new formula for this (since I have to add in that new table with the entry dates), or if I can just tweak what I have to make it work.

[Ashish_Mathur]

Hi,

Why should the revenue be divided equally between the three.  Sara was the AM only for 2.5 months while Kathy was the AM for well over a year.  Also, share the two datasets in an Excel file format.

[Anonymous]

Can you just do a conditional column in power bi or sql and do some kind of check on the sales date per account?

So using your example:

If Saledate Between 12/11/2015 and 1/3/2017 Then Kathy

If Saledate Between 1/3/2017 and 3/21/2017 Then Sara

Then automate further to make a more accurate 

if SaleDate between max and min ownership date then AM = Desired AM

Here's what the table would look like:

You basically need the max and min ownership date per Account Manager to make it work