• 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
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Subscription Recovered|Power Pivot|Excel Forum|My Online Training Hub

You are here: Home / Subscription Recovered|Power Pivot|Excel Forum|My Online Training Hub

vba course banner

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 PivotSubscription Recovered
sp_PrintTopic sp_TopicIcon
Subscription Recovered
Avatar
jon999
Member
Members
Level 0
Forum Posts: 21
Member Since:
September 1, 2016
sp_UserOfflineSmall Offline
1
January 20, 2018 - 1:24 pm
sp_Permalink sp_Print

Hi

 

I am trying to work out if a subscription has recovered during a period.

I have three tables, date, subscription and customers.

If a subscription churns (does not renew) at the end of the subscription period however within a 12 month period returns (from the end date of the subscription), to have a measure that is able to look back over the last 12 months to see if the customer had a subscription. If this is the case, then to be able to calculate what the difference between the old subscription to the new subscription.

For example, have a customer who had a subscription for the period 1 July 2016 to 30 June 2017 for $1,000. At 30 June 2017 the subscription does not renew and therefore churned. However, on the 15 December 2017 that customer returns with a new subscription value of $2,000.

So, need a measure to be able to do the calculation to say for the month of June 2017 you have churn of -$1,000 and December 2017 churn of $1,000 and upsell of $1,000. Therefore, the half year (Jul-Dec) churn $0 and upsell $1,000.

 

Or if the new subscription value is $500 to be downgrade of $500 but the churn numbers to be the same from above.

Or if the new subscription value is $1000 to only show the churn number from above.

 

Thanks

 

Jon

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
January 22, 2018 - 1:54 pm
sp_Permalink sp_Print

Hi Jon,

The formula will depend on how your data is structured - what fields are in what tables and what are the relationships. Please provide a mock up Excel file containing the format of your data so we can help you further.

Thanks,

Mynda

Avatar
jon999
Member
Members
Level 0
Forum Posts: 21
Member Since:
September 1, 2016
sp_UserOfflineSmall Offline
3
January 23, 2018 - 7:28 am
sp_Permalink sp_Print

Hi Mynda

I have attached a mock up file. 

The data is very basic just has the customer name and the subscription amount per month for each customer. I also have a date table and a customer table.

Also in the file I have shown how I have done this using excel formulas, however I think there would be an easier way to do this.

As also, thanks for your help Mynda.

Kind regards

Jon

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
January 23, 2018 - 7:13 pm
sp_Permalink sp_Print sp_EditHistory

Hi Jon,

I is possible, but you have to use a different solution, thinking in PQ is different than regular excel.

I created a function to calculate the Variance, added a column to find the Type of customer, then pivoted he Type using the Varinace values.

Here is the function:

(CustomerName)=>
let
    //CustomerName="Customer 7",
    Source = SourceData,
    LastValue=Table.SelectRows(Source, each (Number.From([Date]) =List.Max(Source[Date])) and ([Customer] = CustomerName)){0}[Amount],
    IsConstant=List.IsEmpty(Table.SelectRows(Source, each
                            ([Amount] <> LastValue) and ([Customer] = CustomerName))[Amount]),
    SelectRows = Table.SelectRows(Source, each
                            ([Amount] <> LastValue) and ([Amount] <> 0) and ([Customer] = CustomerName)),
    NewValue=if IsConstant then LastValue else (if List.IsEmpty(SelectRows[Amount]) then 0 else SelectRows{0}[Amount]),
    Result=if NewValue=LastValue then 0 else LastValue-NewValue
in
    Result

To see what the function does, you can convert it to a query, by adding 2 forward slashes to the first row:

//(CustomerName)=>

then remove the forward slashes from //CustomerName="Customer 7",

You will have to manually type here the customer number, to see the results for that customer only.

I attached the test file too, hope it helps.

Note that for Customer 8, in month 5, you have 60000, but all other months has 40000. Your calculation says that this customer has no change, but my results are showing -20000, a downgrade. Was that an error, or your calculations are correct? That value seems to be unusual.

Avatar
jon999
Member
Members
Level 0
Forum Posts: 21
Member Since:
September 1, 2016
sp_UserOfflineSmall Offline
5
January 24, 2018 - 12:59 pm
sp_Permalink sp_Print

Hi Catalin

Thank you for your reply.

Correct, it was my error in relation to Customer 8. 

Customer 11 and 12 is a combination of churn and upsell/downgrade. So for customer 11 it should be churn of 18,216 and upsell of 6,784. Customer 12 churn of 5,316 and downgrade of -3,751. Is this possible?

Thanks

 

Jon

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
January 24, 2018 - 4:44 pm
sp_Permalink sp_Print sp_EditHistory

Hi Jon,

Only one category can be assigned to a customer, unfortunately. Its still possible I think, but requires a different approach, your sample data has only one category in the result column. How many combinations of categories can be: churn-upsell, churn-downgrade, are there other combinations? How do you calculate based on existing data when a customer falls into one of these combinations of categories? We have to rebuild that conditional column formulas to return all possible combinations in the same column.

In Data query, this is the step you have to edit:

#"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Type", each
        if [#"31/12/2017"] = [#"30/06/2017"] then "No Change"
        else if ([#"30/06/2017"]=0 and [#"30/06/2017"] < [#"31/12/2017"]) then "New Business Or Recovered"
        else if ([#"30/06/2017"]>0 and [#"31/12/2017"]=0) then "Churn"
        else if [#"30/06/2017"] < [#"31/12/2017"] then "Upsell"
        else if [#"30/06/2017"] > [#"31/12/2017"] then "Downgrade"
        else if (condition1 and condition 2 and any other condition) then "Churn | Upsell" 

        else if (condition1 and condition 2 and any other condition) then "Churn | Downgrade" 
        else null),

The next step, Pivoted Column, is using Sum as the aggregate function, it should not use any aggregation.

Lastly, the function that calculates the values should be modified to return 2 values in case of multiple cases : 5316 | -3781, this is tricky.

In case you want to go for such a way to display the results, you have to prepare the conditions for any other category combinations and explain the formulas to calculate for each new category/combination.

Avatar
jon999
Member
Members
Level 0
Forum Posts: 21
Member Since:
September 1, 2016
sp_UserOfflineSmall Offline
7
January 24, 2018 - 5:06 pm
sp_Permalink sp_Print

Hi Catalin

That is where I have been struggling in working out how I can split between churn-upsell and churn-downgrade in separate headings.

For example using Customer 11

The subscription churned in Jun 17 for -18,216 then the subscription comes back in Aug 17 for 25,000. As this is not a new subscription (has to come back within 12 months) then its not churn so at Dec 17 I need churn of 18,216 and upsell of 6,784. So when I look at it at the end of the year all I show is upsell of 6,784.

In the mock file I attached I have a tab called Excel formula which I have stepped it out using Excel formulas but was hoping to do this in POwer Query or Power Pivot.

Thanks

Jon

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
January 24, 2018 - 6:12 pm
sp_Permalink sp_Print

Hi Jon,

I did replicated the Excel Formula calculations in PQ, but there are no calculations there for how to detect the combined categories, that's what I need to help you.

Avatar
jon999
Member
Members
Level 0
Forum Posts: 21
Member Since:
September 1, 2016
sp_UserOfflineSmall Offline
9
January 24, 2018 - 8:04 pm
sp_Permalink sp_Print

Hi Catalin

The way I worked out the combined categories was to use the IF formula to work out if it is a New Business or Recovered. If it returned new business or recovered then look back over the last 12 months to see when the first zero month was. Then take the before month number and add or subtract it to the Dec number. If it is positive then it is an upsell, if a negative a downgrade and the amount from the before month is the positive churn number. I could only work this out by doing multiple steps. Was hoping there was one formula to do it all.

Thanks

Jon

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
10
January 25, 2018 - 2:32 am
sp_Permalink sp_Print sp_EditHistory

Hi Jon,

Hope you can use the attached layout.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
11
January 25, 2018 - 2:55 am
sp_Permalink sp_Print

Or this one.

Cheers,

Catalin

Avatar
jon999
Member
Members
Level 0
Forum Posts: 21
Member Since:
September 1, 2016
sp_UserOfflineSmall Offline
12
January 25, 2018 - 6:54 pm
sp_Permalink sp_Print

Hi Catalin

It works!

Thanks so much for helping me out on this.

Kind regards 

 

Jon

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
13
January 25, 2018 - 8:58 pm
sp_Permalink sp_Print

Which version?

Avatar
jon999
Member
Members
Level 0
Forum Posts: 21
Member Since:
September 1, 2016
sp_UserOfflineSmall Offline
14
January 27, 2018 - 8:19 pm
sp_Permalink sp_Print

v3

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Philip Treacy, Louis Muti
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Charles DeGraffenreaid
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Joao Marques
Regi Hampton
Taffie Elliott
Paramita Chakraborty
Forum Stats:
Groups: 3
Forums: 24
Topics: 6359
Posts: 27806

 

Member Stats:
Guest Posters: 49
Members: 32336
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.