cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrainey Member
Member

Populating To Date Revenue

I have this table:

1.PNG

 

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):

1.PNG

 

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:

1.PNG

 

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!

5 REPLIES 5
Community Support Team
Community Support Team

Re: Populating To Date Revenue

Hi @mrainey

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

mrainey Member
Member

Re: Populating To Date Revenue

@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.

Community Support Team
Community Support Team

Re: Populating To Date Revenue

Hi @mrainey

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]))

6.png

7.png

 

Best Regards

Maggie

mrainey Member
Member

Re: Populating To Date Revenue

@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:


 

1.PNG

 

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

Community Support Team
Community Support Team

Re: Populating To Date Revenue

Hi @mrainey

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

5.png

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