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