Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ^_^
Solved! Go to Solution.
Hi @kuwarisu ,
You can refer the following links to get it:
DAX for Power BI - Easy Cohort Analysis
1. Create a calculated column as below to use as Rows field of visual
2. Create a dimension table as Columns field
3. Create a measure as below to get the percentage
Best Regards
Hi @kuwarisu ,
You can refer the following links to get it:
DAX for Power BI - Easy Cohort Analysis
1. Create a calculated column as below to use as Rows field of visual
2. Create a dimension table as Columns field
3. Create a measure as below to get the percentage
Best Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |