Forum Discussion
Splitting Revenue
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.
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...
- 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)
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.
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.