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