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.

     

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

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's avatar
    PattemManohar
    Community Champion
    7 years ago
    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'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. "