• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Use calendar table to identify dates between start and end date|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Use calendar table to identify dates between start and end date|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryUse calendar table to identify date…
sp_PrintTopic sp_TopicIcon
Use calendar table to identify dates between start and end date
Avatar
Anne Walsh
Member
Members
Level 0
Forum Posts: 100
Member Since:
May 2, 2014
sp_UserOfflineSmall Offline
1
August 6, 2018 - 9:08 pm
sp_Permalink sp_Print sp_EditHistory

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

sp_AnswersTopicSeeAnswer See Answer
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
August 6, 2018 - 9:28 pm
sp_Permalink sp_Print

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

Avatar
Anne Walsh
Member
Members
Level 0
Forum Posts: 100
Member Since:
May 2, 2014
sp_UserOfflineSmall Offline
3
August 7, 2018 - 6:39 pm
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
August 7, 2018 - 8:49 pm
sp_Permalink sp_Print

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.

sp_AnswersTopicAnswer
Answers Post
Avatar
Anne Walsh
Member
Members
Level 0
Forum Posts: 100
Member Since:
May 2, 2014
sp_UserOfflineSmall Offline
5
August 9, 2018 - 6:46 pm
sp_Permalink sp_Print

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.Β 

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
August 10, 2018 - 11:05 am
sp_Permalink sp_Print

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

Avatar
Anne Walsh
Member
Members
Level 0
Forum Posts: 100
Member Since:
May 2, 2014
sp_UserOfflineSmall Offline
7
August 10, 2018 - 8:27 pm
sp_Permalink sp_Print

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 πŸ™‚Β 

Avatar
Anne Walsh
Member
Members
Level 0
Forum Posts: 100
Member Since:
May 2, 2014
sp_UserOfflineSmall Offline
8
August 10, 2018 - 11:18 pm
sp_Permalink sp_Print

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 πŸ™‚Β 

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
9
August 11, 2018 - 7:24 pm
sp_Permalink sp_Print

Very clever! Well done.

Avatar
Anne Walsh
Member
Members
Level 0
Forum Posts: 100
Member Since:
May 2, 2014
sp_UserOfflineSmall Offline
10
August 14, 2018 - 5:30 pm
sp_Permalink sp_Print

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 πŸ™‚

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
11
August 14, 2018 - 9:06 pm
sp_Permalink sp_Print

πŸ™‚ I bet you learnt loads along the way so hopefully the journey was worthwhile.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: SALLY, Valentyn Kristioglo
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Forum Stats:
Groups: 3
Forums: 24
Topics: 6223
Posts: 27294

 

Member Stats:
Guest Posters: 49
Members: 31919
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.