Creating a calculation to see how long it took a person to reach a goal

[eroyle]

Hello,

I am trying to create a calculation in Power BI to calculate how long it took for one person to reach a goal of 12 individual widgets.  A sample of the data (de-identified) is attached.  We have a goal of 12 widgets in 12 weeks, so I'm hoping I can calculate how long it took each "person" to reach this goal of 12 widgets and from there I can filter out those in 12 weeks and even create filters for the others who we would label as Not meeting goal.  Hopefully, this makes sense.  The file is in excel and I do have a calendar table as well.

Hoping to have a column for either "weeks to 12" or if needed "days to 12" and from there I can calculate out weeks.  Thank you in advance!!!

Date	Region	Location	Person Name	Unique Identifier to Count	Person ID
3/6/2023	EMEA	Location ABC	Steven Smith	JKFM3H00570025C	Person 220
9/12/2022	EMEA	Location ABC	John Farrell	JKFM3K007600064	Person 36
12/12/2022	EMEA	Location ABC	John Smith	JKFM3H00570020E	Person 101
3/13/2023	EMEA	Location ABC	John Smith	JKFM3H005700263	Person 101
2/13/2023	EMEA	Location ABC	John Smith	JKFM3H005700247	Person 101
2/13/2023	EMEA	Location ABC	John Smith	JKFM3H005700246	Person 101
8/29/2022	EMEA	Location ABC	John Smith	JKFM3H00570018C	Person 101
12/12/2022	EMEA	Location ABC	John Smith	JKFM3H00570020D	Person 101
2/6/2023	EMEA	Location ABC	John Smith	JKFM3K0076000B6	Person 101
3/21/2022	EMEA	Location ABC	Bill Barr	JKFM3H00570008D	Person 105
3/21/2022	EMEA	Location ABC	Bill Barr	JKFM3H00570008C	Person 105
3/21/2022	EMEA	Location ABC	Bill Barr	JKFM3H00570008E	Person 105
6/20/2022	EMEA	Location ABC	Bill Barr	JKFM3H0057000FE	Person 105
6/20/2022	EMEA	Location ABC	Bill Barr	JKFM3H0057000FF	Person 105
4/4/2022	EMEA	Location ABC	Bill Barr	JKFM3H0057000BE	Person 105
4/4/2022	EMEA	Location ABC	Bill Barr	JKFM3H0057000BB	Person 105
6/20/2022	EMEA	Location ABC	Bill Barr	JKFM3H0057000FC	Person 105
5/9/2022	EMEA	Location ABC	John Doe	JKFM3H0057000EC	Person 71
8/8/2022	EMEA	Location ABC	John Doe	JKFM3K007600048	Person 71
9/19/2022	EMEA	Location ABC	John Doe	JKFM3K00760006C	Person 71
11/21/2022	EMEA	Location ABC	John Doe	JKFM3K007600091	Person 71
9/19/2022	EMEA	Location ABC	John Doe	JKFM3K00760006B	Person 71
6/20/2022	EMEA	Location ABC	John Doe	JKFM3H005700102	Person 71
7/25/2022	EMEA	Location ABC	John Doe	JKFM3H00570015B	Person 71
10/10/2022	EMEA	Location ABC	John Doe	JKFM3K007600071	Person 71
10/24/2022	EMEA	Location ABC	John Doe	JKFM3K007600083	Person 71
11/7/2022	EMEA	Location ABC	John Doe	JKFM3K007600089	Person 71
11/21/2022	EMEA	Location ABC	John Doe	JKFM3K007600093	Person 71
11/21/2022	EMEA	Location ABC	John Doe	JKFM3K00760008F	Person 71
11/28/2022	EMEA	Location ABC	John Doe	JKFM3K007600094	Person 71
1/16/2023	EMEA	Location ABC	John Doe	JKFM3K0076000A9	Person 71
2/13/2023	EMEA	Location ABC	John Doe	JKFM3H00570024B	Person 71
9/19/2022	EMEA	Location ABC	John Doe	JKFM3K00760006A	Person 71
1/23/2023	EMEA	Location ABC	John Doe	JKFM3K0076000AD	Person 71
2/13/2023	EMEA	Location ABC	John Doe	JKFM3K0076000B9	Person 71
8/8/2022	EMEA	Location ABC	John Doe	JKFM3K007600049	Person 71
11/7/2022	EMEA	Location ABC	John Doe	JKFM3K007600088	Person 71
1/30/2023	EMEA	Location ABC	John Doe	JKFM3K0076000AF	Person 71
1/30/2023	EMEA	Location ABC	John Doe	JKFM3K0076000B0	Person 71
6/27/2022	EMEA	Location ABC	John Doe	JKFM3H005700116	Person 71
7/18/2022	EMEA	Location ABC	John Doe	JKFM3H005700146	Person 71
7/18/2022	EMEA	Location ABC	John Doe	JKFM3H005700147	Person 71
9/5/2022	EMEA	Location ABC	John Doe	JKFM3K007600059	Person 71
10/10/2022	EMEA	Location ABC	John Doe	JKFM3K007600077	Person 71
1/16/2023	EMEA	Location ABC	John Doe	JKFM3K0076000A7	Person 71
1/16/2023	EMEA	Location ABC	John Doe	JKFM3K0076000A8	Person 71
2/27/2023	EMEA	Location ABC	John Doe	JKFM3K0076000E4	Person 71
10/31/2022	EMEA	Location ABC	John Doe	JKFM3K007600086	Person 71
11/14/2022	EMEA	Location ABC	John Doe	JKFM3K00760008D	Person 71
1/30/2023	EMEA	Location ABC	John Doe	JKFM3K0076000B1	Person 71
2/27/2023	EMEA	Location ABC	John Doe	JKFM3K0076000DF	Person 71
7/25/2022	EMEA	Location ABC	John Doe	JKFM3H00570015A	Person 71
11/7/2022	EMEA	Location ABC	John Doe	JKFM3K00760008A	Person 71
1/23/2023	EMEA	Location ABC	John Doe	JKFM3K0076000AA	Person 71
6/20/2022	EMEA	Location ABC	John Doe	JKFM3H005700105	Person 71
10/17/2022	EMEA	Location ABC	John Doe	JKFM3K007600079	Person 71
10/31/2022	EMEA	Location ABC	John Doe	JKFM3K007600087	Person 71
12/5/2022	EMEA	Location ABC	John Doe	JKFM3K007600098	Person 71
7/4/2022	EMEA	Location ABC	John Doe	JKFM3H00570011E	Person 71
7/25/2022	EMEA	Location ABC	John Doe	JKFM3H005700158	Person 71
10/17/2022	EMEA	Location ABC	John Doe	JKFM3K007600082	Person 71
2/20/2023	EMEA	Location ABC	John Doe	JKFM3H00570024C	Person 71
2/6/2023	EMEA	Location ABC	John Doe	JKFM3K0076000B2	Person 71
3/20/2023	EMEA	Location ABC	John Doe	JKFM3K0076000ED	Person 71
3/20/2023	EMEA	Location ABC	John Doe	JKFM3K0076000EC	Person 71
3/20/2023	EMEA	Location ABC	John Doe	JKFM3K0076000EB	Person 71
4/25/2022	EMEA	Location ABC	Joanne Doe	JKFM3H0057000DB	Person 107
4/18/2022	EMEA	Location ABC	Joanne Doe	JKFM3H0057000D7	Person 107
11/14/2022	EMEA	Location ABC	Joanne Doe	JKFM3H0057001F1	Person 107
10/17/2022	EMEA	Location ABC	Bob Marley	JKFM3H0057001DB	Person 109
3/28/2022	EMEA	Location ABC	Bob Marley	JKFM3H00570009C	Person 109
4/4/2022	EMEA	Location ABC	Bob Marley	JKFM3H0057000B3	Person 109
4/4/2022	EMEA	Location ABC	Bob Marley	JKFM3H0057000AE	Person 109
4/25/2022	EMEA	Location ABC	Bob Marley	JKFM3H0057000DA	Person 109
4/25/2022	EMEA	Location ABC	Bob Marley	JKFM3H0057000D9	Person 109
3/28/2022	EMEA	Location ABC	Bob Marley	JKFM3H00570009D	Person 109

[eroyle]

Sorry, right after posting I realized I only gave you the fact table.  I also have created a summary table using this and another to have each Person listed only 1 time including their earliest Widget date and the total volume.  Looking for another column HERE that shows Weeks to 12 Widgets and the summarize DAX is here:  

PersonSummaryDetail = SUMMARIZE(Persons,Persons[Person2],Persons[Person Identifier],Persons[Shortened Identifier],'ALL Person'[Certification_Date],'ALL Person'[Earliest WIDGET_DATE],"Total_Widgets",DISTINCTCOUNT(HCA_WIDGETS[WIDGET_ID]))

Shortened Identifier	Certification_Date	Earliest WIDGET_DATE	Procedure Count
Person 36	10/19/2021 0:00	1/31/2022 0:00	107
Person 17	11/8/2021 0:00	11/8/2021 0:00	77
Person 35	11/9/2021 0:00	1/31/2022 0:00	77
Person 47	2/16/2022 0:00	3/7/2022 0:00	65
Person 67	4/25/2022 0:00	4/25/2022 0:00	58
Person 40	10/19/2021 0:00	2/7/2022 0:00	53
Person 6	6/22/2021 0:00	7/19/2021 0:00	53
Person 3	5/19/2021 0:00	6/21/2021 0:00	49
Person 71	11/5/2021 0:00	5/9/2022 0:00	49
Person 10	9/13/2021 0:00	9/13/2021 0:00	46
Person 102	5/2/2022 0:00	5/2/2022 0:00	42
Person 54	10/27/2021 0:00	3/7/2022 0:00	37
Person 24	12/7/2021 0:00	12/13/2021 0:00	36
Person 68	10/15/2021 0:00	5/16/2022 0:00	29
Person 96	11/17/2021 0:00	9/26/2022 0:00	26
Person 31	1/13/2022 0:00	1/17/2022 0:00	25
Person 83	12/30/2021 0:00	4/25/2022 0:00	25
Person 92	9/7/2022 0:00	9/12/2022 0:00	24
Person 82	12/30/2021 0:00	4/25/2022 0:00	23
Person 103	11/3/2021 0:00	10/10/2022 0:00	22
Person 4	5/19/2021 0:00	6/28/2021 0:00	22
Person 79	2/23/2022 0:00	7/4/2022 0:00	22
Person 14	9/29/2021 0:00	10/25/2021 0:00	21
Person 41	10/26/2021 0:00	2/14/2022 0:00	21
Person 16	10/8/2021 0:00	11/8/2021 0:00	20
Person 38	2/6/2022 0:00	2/7/2022 0:00	20
Person 44	11/30/2021 0:00	2/21/2022 0:00	20
Person 63	10/27/2021 0:00	4/25/2022 0:00	20
Person 2	6/16/2021 0:00	6/21/2021 0:00	19
Person 53	12/10/2021 0:00	2/21/2022 0:00	19
Person 111	6/17/2022 0:00	7/4/2022 0:00	18
Person 64	12/7/2021 0:00	4/25/2022 0:00	18
Person 77	4/1/2022 0:00	6/20/2022 0:00	18
Person 113	10/12/2022 0:00	10/31/2022 0:00	17
Person 157	12/6/2022 0:00	1/16/2023 0:00	17
Person 91	8/27/2022 0:00	8/29/2022 0:00	17
Person 15	9/29/2021 0:00	10/25/2021 0:00	16
Person 5	6/22/2021 0:00	7/19/2021 0:00	16

[v-yiruan-msft]

Hi @eroyle ,

As checked your description for some times, I still not clear about your requirement. Do you want to get the required week number when the people reach the goal of 12 widgets? Could you please provide some raw data in the table 'Persons','ALL_Person' and 'HCA_WIDGETS'(exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards