May 2, 2014
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 🙂
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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.
May 2, 2014
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.
1 Guest(s)