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 Number | Date Request Received | Date Request Completed | Total days exclude weekend | Cohort Y | Cohort M# | Cohort M | Cohort 10 | Cohort 20 | Cohort 40 | Cohort 60 | Cohort 90 |
| 211881 | Saturday, January 1, 2022 | Tuesday, January 11, 2022 | 8 | 2022 | 1 | Jan | 1 | 0 | 0 | 0 | 0 |
| 212042 | Saturday, January 1, 2022 | Sunday, March 6, 2022 | 45 | 2022 | 1 | Jan | 0 | 0 | 0 | 1 | 0 |
| 7526 | Saturday, January 1, 2022 | Thursday, January 13, 2022 | 9 | 2022 | 1 | Jan | 1 | 0 | 0 | 0 | 0 |
| 35793 | Saturday, January 1, 2022 | Tuesday, January 18, 2022 | 13 | 2022 | 1 | Jan | 0 | 1 | 0 | 0 | 0 |
| 35799 | Saturday, January 1, 2022 | Wednesday, January 19, 2022 | 14 | 2022 | 1 | Jan | 0 | 1 | 0 | 0 | 0 |
| 35940 | Saturday, January 1, 2022 | Tuesday, March 8, 2022 | 46 | 2022 | 1 | Jan | 0 | 0 | 0 | 1 | 0 |
| 211906 | Sunday, January 2, 2022 | Thursday, January 20, 2022 | 13 | 2022 | 1 | Jan | 0 | 1 | 0 | 0 | 0 |
| 211954 | Sunday, January 2, 2022 | Sunday, February 6, 2022 | 26 | 2022 | 1 | Jan | 0 | 0 | 1 | 0 | 0 |
| 7245 | Sunday, January 2, 2022 | Sunday, January 9, 2022 | 5 | 2022 | 1 | Jan | 1 | 0 | 0 | 0 | 0 |
| 5295 | Sunday, January 2, 2022 | Monday, January 10, 2022 | 5 | 2022 | 1 | Jan | 1 | 0 | 0 | 0 | 0 |
| 5303 | Sunday, January 2, 2022 | Tuesday, January 18, 2022 | 12 | 2022 | 1 | Jan | 0 | 1 | 0 | 0 | 0 |
| 7528 | Sunday, January 2, 2022 | Thursday, January 13, 2022 | 9 | 2022 | 1 | Jan | 1 | 0 | 0 | 0 | 0 |
| 35990 | Sunday, January 2, 2022 | Thursday, March 24, 2022 | 58 | 2022 | 1 | Jan | 0 | 0 | 0 | 1 | 0 |
| 35942 | Sunday, January 2, 2022 | Tuesday, March 8, 2022 | 46 | 2022 | 1 | Jan | 0 | 0 | 0 | 1 | 0 |
| 35068 | Sunday, January 2, 2022 | Monday, January 17, 2022 | 11 | 2022 | 1 | Jan | 0 | 1 | 0 | 0 | 0 |
| 211894 | Monday, January 3, 2022 | Wednesday, March 2, 2022 | 40 | 2022 | 1 | Jan | 0 | 0 | 0 | 1 | 0 |
| 36055 | Monday, January 3, 2022 | Wednesday, May 25, 2022 | 101 | 2022 | 1 | Jan | 0 | 0 | 0 | 0 | 0 |
| 35789 | Monday, January 3, 2022 | Monday, January 17, 2022 | 10 | 2022 | 1 | Jan | 1 | 0 | 0 | 0 | 0 |
| 35806 | Monday, January 3, 2022 | Sunday, January 23, 2022 | 14 | 2022 | 1 | Jan | 0 | 1 | 0 | 0 | 0 |
| 35881 | Monday, January 3, 2022 | Thursday, February 17, 2022 | 32 | 2022 | 1 | Jan | 0 | 0 | 1 | 0 | 0 |
| 35809 | Monday, January 3, 2022 | Tuesday, January 25, 2022 | 16 | 2022 | 1 | Jan | 0 | 1 | 0 | 0 | 0 |
| 36102 | Monday, January 3, 2022 | Tuesday, May 10, 2022 | 90 | 2022 | 1 | Jan | 0 | 0 | 0 | 0 | 0 |
| 211926 | Monday, January 3, 2022 | Thursday, January 27, 2022 | 18 | 2022 | 1 | Jan | 0 | 1 | 0 | 0 | 0 |
| 34845 | Tuesday, January 4, 2022 | Sunday, January 30, 2022 | 18 | 2022 | 1 | Jan | 0 | 1 | 0 | 0 | 0 |
| 34846 | Tuesday, January 4, 2022 | Sunday, January 30, 2022 | 18 | 2022 | 1 | Jan | 0 | 1 | 0 | 0 | 0 |
| 7255 | Tuesday, January 4, 2022 | Thursday, January 13, 2022 | 7 | 2022 | 1 | Jan | 1 | 0 | 0 | 0 | 0 |
| 5298 | Tuesday, January 4, 2022 | Tuesday, January 11, 2022 | 5 | 2022 | 1 | Jan | 1 | 0 | 0 | 0 | 0 |
| 36144 | Wednesday, February 16, 2022 | Monday, May 30, 2022 | 72 | 2022 | 2 | Feb | 0 | 0 | 0 | 0 | 1 |
| 36089 | Thursday, February 17, 2022 | Thursday, April 28, 2022 | 50 | 2022 | 2 | Feb | 0 | 0 | 0 | 1 | 0 |
| 212272 | Monday, February 28, 2022 | Monday, May 30, 2022 | 65 | 2022 | 2 | Feb | 0 | 0 | 0 | 0 | 1 |
| 35240 | Monday, February 28, 2022 | Tuesday, April 26, 2022 | 41 | 2022 | 2 | Feb | 0 | 0 | 0 | 1 | 0 |
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 Days | 20 Days | 40 Days | 60 Days |
| Jan | 71% | 14% | 9% | 6% |
| Feb | 80% | 14% | 4% | 2% |
| March | 85% | 8% | 6% | 1% |
| April | 85% | 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 ^_^
