Finding the right approach for creating an accumulated information table

Question

I have a table in Power BI holding information about different customers. The table has the following columns:

1. ID - The unique ID of the customer.
2. Days since Registration - How many days have passed since the customer registered to the company.
3. Days to Activity- How many days have passed from registration and until the customer became active. If the customer did not become active at any point this should be NULL.

ID	Days since Registration	Days to Activity
11	2	1
22	3	-
33	4	3
44	4	4
55	2	1
66	1	-
77	3	3
88	4	2

Now I want to create a table which will show the percent of customers who had become active on each number of days since registration, out of all customers who have been registered for this number of days. For example, the table below:

Day	Customers registered	Active customers	% active
1	8	2	25%
2	7	3	43%
3	5	3	60%
4	3	3	100%

Notice that All 8 customers have been registered for 1 day or more. Out of them 2 customers have become active in 1 day. 7 customers have been registered for 2 days or more. Out of them 3 have become active during their first 2 days.

I have really struggled with thinking about how I can produce this. Would really appreciate any tips about the approach I should use or specific measures I should create to produce the table below. Thanks

Answer 1

First create a table of the days...

let
a = List.Min(Table[Days since Registration]),
b = List.Max(Table[Days since Registration]),
Source = List.Numbers(a, b),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Days"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Days", Int64.Type}})
in
#"Changed Type"

then create the measures :

Customers Registered = 

var a = SELECTEDVALUE(Days[Days])

 return 

 CALCULATE(
 COUNT('Table'[ID ]),
      filter(ALLEXCEPT('Table','Table'[ID ]), 
      
      a <= 'Table'[Days since Registration]) )

.

Active customers = 

var a = SELECTEDVALUE(Days[Days])

return 

CALCULATE(
COUNT('Table'[ID ]),
      filter(ALLEXCEPT('Table','Table'[ID ]), 
      
      a > 'Table'[Days to Activity]),
      a <= 'Table'[Days since Registration] 
       
       )

.

% active = [Active customers] / [Customers Registered]