I've got a list of subscriptions with a start date and end date and I need to show an allocated percentage they have earned each day e.g. a 365 dollar subscription is 100% deferred, that means that 1 dollar is earned per day.
I've got a power query model set up - I've used unpivot to generate a set of dates for every single subscription. However it's humongously large and slow although it does work..
So I was looking at using a calendar table with it instead. So far I've created the calendar and merged it the with the transaction file on the End date column in the transaction file. However I'm running into problems when I go to add my M formula to identify if the calendar date on that row is between the start date and end date of the subscription. If yes, then it's a "Revenue date" if not, then it's "not revenue date". I plan to use this result for further calculations. When I refresh it's not picking up my amended calendar. I reckon is my issue is identifying what fields do I link it on? Or is there something else altogether that I am missing.. I used a full outer join
Hi Anne,
I'm not sure this is the best approach to this problem. Can you please expand on the allocated percentage you want to calculate and subsequent calculations you mentioned. If you can provide a few samples of the desired result, maybe we can come up with an alternate approach to creating the calendar table.
Mynda
hi Mynda
Thanks for this. I'm attaching a sample file - it doesn't have all the calculations that I currently have (e.g nothing for partially deferred income) but basically I added the dates for 2 years, unpivoted and then used PQ formulas to calculate the various daily income options. For a small clean data set (as I have here) it actually works fine. The next step would be to pivot it and use a parameter query (thanks to your brilliant course for that!) to identify the earned/deferred income on various dates. The idea is that the user would be able to - for example enter a date and identify what income has been earned at that date and what is deferred i.e. paid for but not yet "earned". The issue is that there's 5 years data and this unpivot solution then gets really really unwieldy..so that's why I was looking at the calendar. Any insights would be most appreciated 🙂 Thank you as always
Hi Anne,
I think this is a Power Pivot problem, not a Power Query problem. If the end goal is to have a user see the earned and deferred income for one date, then you don't want to generate every permutation as this would be oodles of values and resulting data. Instead wouldn't it make more sense just to calculate the values as required on the fly?
How would you approach this with regular Excel? Could you write a formula that did this for a single date as required?
Mynda
P.S. I still don't understand the logic behind the Subscription Deferred % values in column D of the Sample Data sheet. There is no pattern to them in regards to how the relate to the start date, end date and years columns. I'm starting to wonder if it even is a value you calculate, or maybe you just know it's deferred.
The subscription deferred references a vlookup that says if it's X subscription, then it's 0 deferred i.e. the day the invoice was sent was the day the income was "earned", if it's 100% deferred, the income is accrued day by day between the start date and end date so if it was 365 starting on 1/1/2018, at 31/1/2018 the "earned" income would be 365 X 31/365 i.e 31 and the "deferred" income would be 334. On 28/2/2018, earned income would be (31+28)/365 multiplied by 365 = 59. Deferred income would now be 306. Then I have another one that has 20% deferred i.e. 80% earned on the day of invoicing (Start date) and the other 20% divided up over the remainder of the subscription period. I've got vlookup that does that in the source data . I got all those formulas working fine in the query believe or not 🙂 in PQ - thanks to your excel-lent M if primer. 🙂
I actually got it all working but it's exactly as you said...LOADS of rows and fragile when extra data is added and very very slow..
So instead of me doing an unpivot of a date for every single invoice from 2013 to 2019 so that the revenue can be calculated daily how can I link this to a single column calendar in PowerPivot that basically says...
Take this date in the calendar table , identify if it is between the start date and end date in this row in the subscription table , if it is then it's a "Revenue Day" and then other formulas flow from that. What field in the Subscription table should I link the Calendar table to? Start Date? End Date? or something else?
Thank you as always.
Hi Anne,
Thanks for clarifying how you arrived at the % Subscription deferred. It was driving me a little crazy 🙂
There is a comprehensive post on how to use Power Pivot to calculated deferred revenue here. I suspect you can probably simplify what is described in that post assuming you don't need everything they cover, but you mentioned earlier that you would have further calculations to do, so maybe they're covered too.
Hope that points you in the right direction, but shout if you get stuck.
Mynda
No, there's a method there in the deferred income - got all those formulas working. 🙂 Totally understand why it would bug you. Went out for long walk yesterday and realised that if I plugged the parameter query date into the formulas (which I tested this morning and got working) it would do what I needed without the need for a mother and father and 22 kids of a calendar table. However, will definitely have a look at that blog post you mentioned. Thank you so much for your help 🙂
Got it all working without the use of a calendar table after all. Lashed in the parameter query into the formulas and it works much much more quickly now 🙂 Thank you again so much 🙂
Very clever! Well done.
Thanks - got there eventually by the very very scenic route 🙂 Of course now I'm looking at it and thinking I should have done most of the formulas in Excel...sigh...but thanks again 🙂
🙂 I bet you learnt loads along the way so hopefully the journey was worthwhile.
