Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
viacka
Advocate I
Advocate I

Calculate user's lifetime for software product

Hey Power BI gurus!

 

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)

 

Final goal:

Calculate user’s actual lifetime based on user’s purchase and license extension history (for all products as well as for selected products)

User's lifetime example

 

Business context:

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.

 

Assumptions:

  • 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

Sample data:

Calculate user's lifetime (sample data).pbix

 

Expected outcome:

  • 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])
7 REPLIES 7
v-shex-msft
Community Support
Community Support

Hi @viacka ,

You can consider to use gantt chart to achieve your requirement.
Create gantt chart with user as task, license as legend, 'license valid from' to start date,  'license valid end' to end date.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin,

 

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

 

Kind regards,

Vic

HI @viacka ,

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

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Like this?

Capture.PNG

 

This is fairly easy to do if you use PowerQuery to expand your Sales table so that it includes the day in between your 'Valid from' and your 'Valid to' dates, like so:

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Licenced Term", Int64.Type}}),
    #"Extracted Date" = Table.TransformColumns(#"Changed Type",{{"Date", DateTime.Date, type date}}),
    #"Added Custom" = Table.AddColumn(#"Extracted Date", "Custom", each List.Dates([Date],[Licenced Term], #duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom", "LicencedDays"}})
in
    #"Renamed Columns"

 

And then you can just use this DAX measure:
=DISTINCTCOUNT(Sales[LicencedDays])

 

See this example file

 

I'd love to see an all-DAX solution though...maybe @MattAllington  has the solution.

 

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.

 

Days Licenced = 
SUMX (
    'Date',
    SUMX (
        SUMMARIZE(data,Data[User's name],Data[Product purchased]),
        VAR CheckDate = 'Date'[Date]
        RETURN
            IF (
                COUNTROWS (
                    FILTER (
                        CALCULATETABLE(Data),
                        CheckDate >= Data[Transaction date]
                            && CheckDate <= Data[Transaction date] + Data[License term (days)] - 1
                    )
                ) >= 1,
                1
            )
    )
)

If you can live without the totals, then this will give the same result when filtered by User and Product

Days Licenced 2 = 
SUMX (
    'Date',
    if(AND(ISFILTERED(Data[Product purchased]),ISFILTERED(Data[User's name])),
        VAR CheckDate = 'Date'[Date]
        RETURN
            IF (
                COUNTROWS (
                    FILTER (
                        CALCULATETABLE(Data),
                        CheckDate >= Data[Transaction date]
                            && CheckDate <= Data[Transaction date] + Data[License term (days)] - 1
                    )
                ) >= 1,
                1
            )
    )
)

And if I ignore which product was purchased, and just look at the existence of any licence at all, then I wrote this

Days Licenced 3 = 
SUMX (
    'Date',
    SUMX (
        VALUES(Data[User's name]),
        VAR CheckDate = 'Date'[Date]
        RETURN
            IF (
                COUNTROWS (
                    FILTER (
                        CALCULATETABLE(Data),
                        CheckDate >= Data[Transaction date]
                            && CheckDate <= Data[Transaction date] + Data[License term (days)] - 1
                    )
                ) >= 1,
                1
            )
    )
)

And here are my results

image.png

Now if the OP can confirm if it is right, then I might write a blog about it.



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Jeff and Matt,

 

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.

lifetime calculation example (correct, incorrect).jpg

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

 

DateLicenseLicenced TermUserName
1/2/2019A2A
1/5/2019B10A
1/14/2019C2A
1/14/2019C2B
1/15/2019C10B
1/16/2019C4B

 

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?

 

Kind regards,

Vic

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.