I tried to post this before but got a message about how it couldn't be saved because I had copied and pasted from Word or some other WYSIWYG editor (which I actually hadn't...)
Anyway, hope this one works. How do I set up the following scenario in PowerPivot (assuming I have a date table to go with it. )
List of customers and how much they have been billed. e.g. 12000.
Same customers and lists of contracts and types of revenue options. (So three tables in all)
Revenue Option 1
They have signed up for a contract (usually 12 months), let's say starting 12/2/2018. How can I show the actual revenue "earned" each month e.g. for February it should be 12,000 * (16/365), for March it should be 12000 * (31/365)
Revenue Option 2
The customer signs up for a contract on 1/3/2018. e.g 10000. 80% of this will be revenue for March 2018. The balance will be divided over March plus the remaining 11 months. Again, how can I show the actual revenue "earned" each month?
What Powerpivot function should I be using to calculate these amounts? Thank you 🙂
Hi Anne,
Can you please prepare a sample file with your tables?
You mentioned that a contract is usually 12 months, is this information in one of the tables? If not, how do we know if it's a 12 months contract or not, and what percentage will be applied in the first month: is it going to be 80% on all contracts, and the remaining amount must be split into the rest of the months?
A few manual examples on the most relevant records will help us understand what you're after.
hi
Just quick update on this. I figured it out by using a different column for various types of subscription e.g. one for all deferred, one for 100% deferred and another for varying percentage deferred linked to the Amount column. Then another column to get the total. I used if/And combinations to get the amount in. Then a pivot to pull it together. I used Power Query to create a table that had a column of individual dates for each contract (using unpivot columns) and I got all that working. Now, they want to bring in 5 years data so that would by 365 X 6 X number of customers e.g. 250 i.e. 438000 rows. So far so good. However, now I'm thinking would this be better in PowerPivot..with a calendar table but would I still need to have that huge table with it...I'm thinking probably yes...My issue at the moment is that it's horrendously slow...any suggestions..thanks.
