Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 between each persons [entry date] and [exit date].
I attempted to write a measure doing so but it didn't work correctly. Was dividing everybody's total by 3 for some reason:
Solved! Go to Solution.
@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.
Proud to be a PBI Community Champion
Proud to be a PBI Community Champion
@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.
Proud to be a PBI Community Champion
@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.
Proud to be a PBI Community Champion
@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...
Proud to be a PBI Community Champion
@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.
Proud to be a PBI Community Champion