Here’s a tricky puzzle I need your help with. I’m trying to properly calculate user’s lifetime given the business specifics, however I haven’t found a straightforward solution yet. Below is a task description, sorry for lots of words)
Calculate user’s actual lifetime based on user’s purchase and license extension history (for all products as well as for selected products)
Let’s say some company ABC sell desktop software apps under some non-typical licensing model. Each user can purchase either App1 or App2 or both at any time. Once the user purchases some app, a license key is generated for each app purchased and each key is valid for the next three months after the purchase date. Each license key expires after three months, and the user is up to decide whether he wants to extend his/her license subscription after its expiration (i.e. unlike most SaaS services, the license keys are not extended automatically). The customer is offered to extend each license key manually for one, two or three years. Once the license key is renewed, it is respectively valid for one, two or three years and after its expiration date the user is offered to extend the license subscription again, and the cycle repeats.
1 license key = 1 license subscription
Lifetime in this case is a timespan when a user had at least one active license subscription
Each app can be purchased independently at any time. New license key is generated with each initial license purchase. User is allowed to have several license keys for the same app
Similarly, each license key can be extended at any time after the initial purchase
The license subscription can be extended before its expiration date. In this case the subscription is valid from its actual expiration date for the respective period purchased
1 month = 30 days, 1 year = 365 days, 2 years = 730 days, 3 years = 1095 days
The tricky part:
How do I calculate subscription’s extension time, i.e. fill ‘license valid from’ and ‘license valid till’ fields? The license should be valid from its current expiration date, and not necessarily from the extension purchase date.
The key difficulty to properly calculate user’s lifetime is that the user can have multiple active license keys for different apps that were issued or extended at different dates. It’s not straightforward how to find the timespan where the user didn’t have any active licenses
Lifetime of John Doe is 604 days (698 days total timespan from the first purchase [1/1/2018] to the latest license expiration date [11/30/2019] less 94 days timespan with no active license [from 5/30/2018 till 9/1/2018])
Lifetime of James Smiths is 910 days (971 days total timespan from the first purchase [1/1/2018] to the latest license expiration date [8/29/2020] less 61 days timespan with no active license [from 4/1/2018 till 6/1/2018])
thank you for your prompt response. I do understand your point however that doesn't solve my problem
Let me clarify a bit. Actually the image in my post with lifetime example calculation as well as data sample are provided for illustrative purposes only. Consider that I have hundred thousands of records so I need some better solution to calculate customer lifetime rather than using Gantt chart
Summing up my original post, I need community's help with the following:
formula ideas how to calculate fields 'license valid from' and 'license valid till' given that license should be valid from its current expiration date, and not necessarily from the purchase date. It can be easily done in Excel however it gets trickier in Power BI
measures to calculate customer lifetime, either for specific products or customer total lifetime. I believe I don't have proper solution to address time overlapping in Power BI that is required here
Hope it gets clearer now. Please ask for additional info if anything needs to be clarified further
#1, Power bi visuals not support to analysis duration graph generated by multiple date fields. You need to create a related bridge table to expand details rows, then visual can auto analytics these records and summarize based on it date range.
#2, In my opinion, you can consider to create variable to store summarize table to manually summary these records, then do iteration on these records to get total amount.
Community Support Team _ Xiaoxin Sheng If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
For learning resources/Release notes, please visit: | |
Well I have a solution, but it doesn't match the numbers the OP mentions. I guess it depends on the logic. My assumption was that the OP wants to know the number of days each person held a licence for each product. With that in mind, and renaming the licence table in the sample to be called "data" and removing the relationship in the model, this is my formula.
thank you for your solution ideas. You are both very close to what I described above, however some tweaking is required to complete this task
1. Both your solutions work perfectly with overlapping time ranges when calculating total customer lifetime or lifetime for a particular product
2. As @MattAllington mentions, his results don't match my numbers due to the business logic. Yes, that's right. I'll try to illustrate the difference with an example below.
Both your solutions imply that the license starts from the purchase date. Well, while that's true for a new license, its actual extension start date may differ from its extension order date. Let's say I purchased a 1-year license on January 1st 2019, and later on May 1st 2019 I extended the license for another 12 months. In this case the license will be valid from January 1st 2019 trough December 31st 2020, i.e. the second order extends the license for the entire 2020 rather than from its original transaction date.
Simply put, if a user extends some license key before its expiration date, the license should be extended from its actual expiration date. After some license key extention takes place, each license key (field "License key") needs to be verified whether extension took place before or after its actual expiration date. So one needs to 'update' license key's expiration date for each distinct license key with each transaction.
In our particular case the situation is similar. Matt's solution advises James Smiths's lifetime for App 2 of 426 days, while it should return 820 days.
@JeffWeir , you can reproduce similar situation in your sample data by adding a couple of order lines for the C license as shown here, for example.
3. @JeffWeir , I have a performance related question since your approach implies a significant number of records for an exploded sales table. We have about 500k order records with an average of 365 days licensed term each, which means that our exploded sales table will contain 500k*365=182.5M records. How efficient is that solution compared to what @MattAllington suggests?
Hi Vic. That's a much trickier problem. If using PowerQuery, I suspect this will need to use the poorly documented List.Accumulate function, given the potentially recursive nature of the challenge. Unfortunately I don't have time currently to investigate further.
It makes for an interesting DAX challenge that would require more DAX smarts than I currently have time to master.