Populating To Date Revenue

[Anonymous]

I have this table:

So it is a company that has had 3 different managers over time as you can see by their entry/exit dates. The Revenue column is just all revenue earned thru the company. The AM revenue column is the money earned by each AM while they owned the account. The formula for it looks like this ([Extended] is the revenue FYI):

Also as a side note, I had to create the [exit date] column since only the entry date was provided and that looks like this:

So while this is mostly working as intended, there's one issue. Since Latricia is the current account owner, I am not getting a $ value for her and I'm assuming it's because she currently has no exit date for the formula to reference while the other two AM's do. How can I modify my formula(s) to get her $ value populated to see what she has made since taking over the account? Like sticking in some kind of dummy date far in the future that will then get replaced by her actual exit date when that time comes. Or whatever works. Thanks!

[v-juanli-msft]

Hi @Anonymous

If your dataset is like mine, just create a calculated column like this 

Column = CALCULATE(SUM(Sheet2[Extended]),ALLEXCEPT(Sheet2,Sheet2[Co],Sheet2[Payroll AM]))

Best Regards

Maggie

[Anonymous]

@v-juanli-msft this sort of works, but does not take into account any date fields so therefore everybody is just given the same amount which isn't correct. It's just taking the total amount and dividing by 3 since there are 3 AM's:

All of their amounts should differ. So the invoice dates need to fall within the parameters of their entry and exit dates.

[v-juanli-msft]

Hi @Anonymous

Possblities why the formula doesn't work on your site lists as below:

1. don't summarize the column when adding it to the table visual

2.

"Extended" column doesn't exsit as my dataset(the screenshot in my lastest post).

Please show me how you table structure.

3.

could you show me the formula"ActualAM" you write?

Best Regards

Maggie 

[v-juanli-msft]

Hi @Anonymous

First i need to know exactly about your table structure, from your formula, you table "M3 Billing" is like this, right?

Co	Payroll AM	Entry Date	Extended	Invoice Date
11427	Ka	12/11/2015	1	12/12/2015
11427	Ka	12/11/2015	2	12/13/2015
11427	Ka	12/11/2015	3	12/14/2015
11427	Sa	1/3/2017	1	1/3/2017
11427	Sa	1/3/2017	2	1/4/2017
11427	Sa	1/3/2017	3	1/5/2017
11427	La	3/21/2017	3	3/23/2017

Best Regards

maggie

[Anonymous]

@v-juanli-msft that is correct. As of right now, I have it partially working. The part I can't get to work is assigning the dollar value to the person who currently owns the account. The previous 2 AM's have their amounts because the formula has entry and exit dates to work with, so it can look for the invoice dates between their entry and exit dates and determine how much they made while they had the account in their name. The current account manager does not have an exit date yet, so therefore it is returning a blank value as seen in the far right column at the bottom.