Urgent help needed with weekly Cohort Report of user activities

[Anonymous]

I am looking to create a cohort report that captures what percentage of each cohort completes an activity each week. 

I have 2 data tables:

User Table (inactive relationships to the calendar table)

UserID	Cohort	Cohort Start date
1	A	1/31/2020
2	A	1/31/2020
3	A	1/31/2020
4	A	1/31/2020
5	B	2/14/2020
6	B	2/14/2020
7	B	2/14/2020
8	B	2/14/2020
9	B	2/14/2020
10	B	2/14/2020
11	B	2/14/2020

UserActivity Table: (I only want to count Activity Status = 1) 

UserID	ActivityStatus	Date Updated
1	1	1/31/2020
1	1	2/1/2020
2	1	2/3/2020
3	1	2/3/2020
3	1	2/3/2020
2	1	2/11/2020
3	1	3/17/2020
5	1	2/17/2020
6	2	2/16/2020
7	2	2/16/2020

I would like a matrix that looks like this -- where you counting distinct userIDs for each week and counting what percent of the total cohort number have a status ID of 1 in that week:

Cohort	Total at Start (Count of cohort)	Cohort Start Date	W1	W2	W3	W4	W5	W6	W7	W8	W9
A	4	1/31/2020	25%	50%	25%	0%	0%	0%	0%	25%
B	7	2/16/2020	0%	14%	0%	0%	0%

[amitchandak]

from the user, table take cohort and cohort start date and take a week from date dim joined to activity on col.

[Anonymous]

@amitchandak I get what you are saying, but not sure how to actually create that dimension. Is that a calculated column and then a measure counting unique IDs? Confusing given information is over 2 tables 

[Greg_Deckler]

So, you will need a week number column, use WEEKNUM then you could create a measure something along the lines of:

Measure =
  VAR __Count = COUNTROWS(FILTER('Table',[ActivityStatus] = 1))
  VAR __CountAll = COUNTROWS(ALL('Table'))
RETURN
  __Count / __CountAll

[Anonymous]

@Greg_Deckler thanks - is there a way to make the weeknum column relative though? so week num 1 is 1/31 for Cohort A but is ALSO num 1 on 2/14 for Cohort B? 

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

- is there a way to make the weeknum column relative though? so week num 1 is 1/31 for Cohort A but is ALSO num 1 on 2/14 for Cohort B? 

Yes,create a calculated column as below:

Column = "w"& INT(DIVIDE(DATEDIFF( CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[Cohort]=EARLIER('Table'[Cohort]))),'Table'[Date],DAY),7)+1)

Finally you will see:

Here is a demo you can refer to.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Greg_Deckler]

I can't really think of a way given your current data structure. Unless perhaps you created a column in your table like:

Column = LOOKUPVALUE('Table1'[Cohort],'Table1'[UserID],'Table2'[UserID])

Then perhaps something like:

Count Column = COUNTROWS(FILTER('Table2',[Date Updated] <= EARLIER([Date Updated] && [ActivityStatus] = 1 && [Column] = EARLIER([Column])))