Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Splitting Revenue

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 bet...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
PattemManohar
Community Champion
7 years ago
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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      PattemManohar
      Community Champion
      7 years ago
      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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.