Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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)
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:
The tricky part:
Sample data:
Calculate user's lifetime (sample data).pbix
Expected outcome:
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
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:
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
Like this?
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])
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
Now if the OP can confirm if it is right, then I might write a blog about it.
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.
@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.
Date | License | Licenced Term | UserName |
1/2/2019 | A | 2 | A |
1/5/2019 | B | 10 | A |
1/14/2019 | C | 2 | A |
1/14/2019 | C | 2 | B |
1/15/2019 | C | 10 | B |
1/16/2019 | C | 4 | B |
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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |