Forum Discussion

kuwarisu's avatar
kuwarisu
New Member
4 years ago
Solved

Cohort Analysis

Hi there,

 

I'm new in this community, I'm trying to create a cohort analysis, I easly achived that in excel but I'm strugling with PBI. Below a sample data 

 

Request NumberDate Request ReceivedDate Request CompletedTotal days exclude weekendCohort YCohort M#Cohort MCohort 10Cohort 20Cohort 40Cohort 60Cohort 90
211881Saturday, January 1, 2022Tuesday, January 11, 2022820221Jan10000
212042Saturday, January 1, 2022Sunday, March 6, 20224520221Jan00010
7526Saturday, January 1, 2022Thursday, January 13, 2022920221Jan10000
35793Saturday, January 1, 2022Tuesday, January 18, 20221320221Jan01000
35799Saturday, January 1, 2022Wednesday, January 19, 20221420221Jan01000
35940Saturday, January 1, 2022Tuesday, March 8, 20224620221Jan00010
211906Sunday, January 2, 2022Thursday, January 20, 20221320221Jan01000
211954Sunday, January 2, 2022Sunday, February 6, 20222620221Jan00100
7245Sunday, January 2, 2022Sunday, January 9, 2022520221Jan10000
5295Sunday, January 2, 2022Monday, January 10, 2022520221Jan10000
5303Sunday, January 2, 2022Tuesday, January 18, 20221220221Jan01000
7528Sunday, January 2, 2022Thursday, January 13, 2022920221Jan10000
35990Sunday, January 2, 2022Thursday, March 24, 20225820221Jan00010
35942Sunday, January 2, 2022Tuesday, March 8, 20224620221Jan00010
35068Sunday, January 2, 2022Monday, January 17, 20221120221Jan01000
211894Monday, January 3, 2022Wednesday, March 2, 20224020221Jan00010
36055Monday, January 3, 2022Wednesday, May 25, 202210120221Jan00000
35789Monday, January 3, 2022Monday, January 17, 20221020221Jan10000
35806Monday, January 3, 2022Sunday, January 23, 20221420221Jan01000
35881Monday, January 3, 2022Thursday, February 17, 20223220221Jan00100
35809Monday, January 3, 2022Tuesday, January 25, 20221620221Jan01000
36102Monday, January 3, 2022Tuesday, May 10, 20229020221Jan00000
211926Monday, January 3, 2022Thursday, January 27, 20221820221Jan01000
34845Tuesday, January 4, 2022Sunday, January 30, 20221820221Jan01000
34846Tuesday, January 4, 2022Sunday, January 30, 20221820221Jan01000
7255Tuesday, January 4, 2022Thursday, January 13, 2022720221Jan10000
5298Tuesday, January 4, 2022Tuesday, January 11, 2022520221Jan10000
36144Wednesday, February 16, 2022Monday, May 30, 20227220222Feb00001
36089Thursday, February 17, 2022Thursday, April 28, 20225020222Feb00010
212272Monday, February 28, 2022Monday, May 30, 20226520222Feb00001
35240Monday, February 28, 2022Tuesday, April 26, 20224120222Feb00010

 

I'm trying to caclaulte the percentage of requested completed within under 10, 20, 40, 60 and 90 days to see how efficient the team, example below.

 

 10 Days20 Days40 Days60 Days
Jan71%14%9%6%
Feb80%14%4%2%
March85%8%6%1%
April85%14%1%0%

 

I used some formulas to seperate the year from the month and to convert the month to a number Jan=1, Feb=2 ..etc

Also I calculated the number of request proceed under cohort 10,20,40,60,90 if it its equal or within the formula will out put "1".

 

Thanks ^_^