Splitting Revenue

[Anonymous]

I have this table: 

I need to write a measure that calculates what each person made when they owned the account. So basically, the [invoice date] (not pictured) would need to fall in between each persons [entry date] and [exit date].

I attempted to write a measure doing so but it didn't work correctly. Was dividing everybody's total by 3 for some reason:

[PattemManohar]

@Anonymous Sample data will be helpful to create revenue measure. Here is my identifications about the calculation that you are using - As the entry and exit date overlap for one day (Example - Kathy exit date is same as Sara Entry date) so you need to change the date comparison logic a bit either >entrydate and <=exitdate or >=entry date and <exitdate. Then, give a try by having ALL(TableName) as first parameter in the FILTER function.

[Anonymous]

@PattemManohar sure thing. So a little more background. I had 1 table that contains all companies and their revenue over the years:

 

Another table in the same database that contains nothing but the companies and the AM's that have managed them over the years. I merged the 2 tables based on the [Co] field so it results in this, before any written formulas:

Then in my attempt to split their revenue appropriately I wrote this formula:

Which then gave me these results:

Which seems like it's working correctly, but for whatever reason it's undercutting everybody's value. I've discovered that if I multiply everybody's value by 3, THEN I get the values I'm looking for. My guess is, it's dividing everybody's amount by 3 because the original [Extended] column is splitting the revenue 3 ways since there's been 3 AM's, but I don't know how to get around that. I tried creating an additional measure to count the amount of AM's per company and multiplying by that number, but it didn't come out correctly.

[PattemManohar]

@Anonymous Do you want to split the Total Revenue among the AM's who managed. (OR) You want to calculate the Total Revenue that was generated for their respective period as AM. Please try to post the sample data in copiable format to suggest any solution.

[Anonymous]

@PattemManohar the latter. So we know Latricia currently owns the account and has since 3/21/2017. If I put a filter on my billing table looking at all revenue after 3/21/2017, I get this:

 

So I know for a fact Latricia should have $53,075.85 as her total. I can't figure out why given the formula I've written, it's only returning a value of:

 

So if I do $17,691.95 * 3, it gives me $53,075.85. I'm confused on why it's just not returning the $53,075.85 value for her.

And I wish I could share my report but it's hooked into our SQL database.

[PattemManohar]

@Anonymous As I've mentioned in above post - Did you tried changing your DAX considering this "As the entry and exit date overlap for one day (Example - Kathy exit date is same as Sara Entry date) so you need to change the date comparison logic a bit either >entrydate and <=exitdate or >=entry date and <exitdate. "

[Anonymous]

@PattemManohar I haven't because I don't think that will work. How would one day of overlap bring a number that is $17,691.95, to $53,075.85? That would mean that one day of overlap would have $35,383.90 which isn't the case. What I need to figure out is why everybody's total is being divided by 3...

[PattemManohar]

@Anonymous Hmm ok, Could you please post the sample data for Invoice table as per above test data, then I can have a look into it (I already have AM table with their entry and exit dates)

[Anonymous]

@PattemManohar Sure. So the billing table will reference Sara Miller's dates because her list is short:

So as you can see by the billing table on the left, between 1/3/2017 - 3/21/2017, company 11472 generated $8.028.21 which should be Sara's number. But instead, it is returning $2,676.07.

I would also imagine that everybody's totals would return correctly if the [Revenue] column on the far right read $261,414.57 for each person rather than being split 3 ways as $87,138.19. That is where I think the undercut numbers are coming from.

[PattemManohar · Accepted solution]

@Anonymous I've tried with below sample Invoice data....

Then try with below expression as a "New Column" in your AMs table

Revenue = CALCULATE(SUM(Test66Invoices[Revenue]),FILTER(Test66Invoices,Test66Invoices[InvoiceDate]>=Test66Lkp[EntryDate] && Test66Invoices[InvoiceDate]<Test66Lkp[ExitDate] && Test66Lkp[Code] = Test66Invoices[Code])) 

Added another condition to check the Code as well if incase if you have multiple codes as well.