Trying to define a planned progress based on individuals name.

[mooneyalan4]

Okay, thanks for any help anyone can lend here. I've spent way too long trying on my own to get this working but remain unable to so far.

My main table is called 'Structured Talent Development' and it has a column called 'Names'

There are other columns tracking programme completion hours and percentages. One of these is called 'Actual Progress' and shows their current '% Completed'

Each person in the 'Names' column will have one of 3 rates of 'Planned Progress' per the table at the bottom. I can assign these based on 'names' column through a table like this one:

Name	ProgressRate
Employee 1	A
Employee 2	A
Employee 3	B
Employee 4	B
Employee 5	B
Employee 6	B
Employee 7	A
Employee 8	A
Employee 9	A
Employee 10	A
Employee 11	C
Employee 12	B
Employee 13	A
Employee 14	C
Employee 15	C

This table has a relationship with 'Structured Talent Development' based on 'Names' and it is <Many to One>

What I am struggling with is referring to this table to pick the planned % based on todays date.

	A	B	C
01/10/2023	8.44%	5.47%	6.92%
15/10/2023	12.44%	7.96%	10.06%
29/10/2023	16.44%	10.45%	13.21%
12/11/2023	17.11%	11.19%	14.15%
26/11/2023	17.78%	11.94%	15.09%
10/12/2023	21.56%	14.18%	17.92%
24/12/2023	25.33%	16.42%	20.75%
07/01/2024	26.89%	18.16%	22.01%
21/01/2024	28.44%	19.90%	23.27%
04/02/2024	32.22%	24.13%	23.90%
18/02/2024	36.00%	28.36%	24.53%
03/03/2024	39.11%	31.84%	25.16%
17/03/2024	42.22%	35.32%	25.79%
31/03/2024	45.78%	39.30%	27.04%
14/04/2024	49.33%	43.28%	28.30%
28/04/2024	53.78%	48.26%	34.59%
12/05/2024	58.22%	53.23%	40.88%
26/05/2024	60.44%	55.72%	44.03%
09/06/2024	62.67%	58.21%	47.17%
23/06/2024	64.00%	59.70%	49.06%
07/07/2024	65.33%	61.19%	50.94%
21/07/2024	66.44%	62.44%	52.52%
04/08/2024	67.56%	63.68%	54.09%
18/08/2024	68.44%	64.68%	55.35%
01/09/2024	69.33%	65.67%	56.60%
15/09/2024	70.67%	67.16%	58.49%
29/09/2024	72.00%	68.66%	60.38%
13/10/2024	74.44%	71.39%	63.84%
27/10/2024	76.89%	74.13%	67.30%
10/11/2024	77.78%	75.12%	68.55%
24/11/2024	78.67%	76.12%	69.81%
08/12/2024	80.67%	78.36%	72.64%
22/12/2024	82.67%	80.60%	75.47%
05/01/2025	83.56%	81.59%	76.73%
19/01/2025	84.44%	82.59%	77.99%
02/02/2025	86.00%	84.33%	80.19%
16/02/2025	87.56%	86.07%	82.39%
02/03/2025	89.11%	87.81%	84.59%
16/03/2025	90.67%	89.55%	86.79%
30/03/2025	91.78%	90.80%	88.36%
13/04/2025	92.89%	92.04%	89.94%
27/04/2025	94.00%	93.28%	91.51%
11/05/2025	95.11%	94.53%	93.08%
25/05/2025	96.67%	96.27%	95.28%
08/06/2025	97.44%	97.14%	96.38%
22/06/2025	98.22%	98.01%	97.48%
06/07/2025	98.56%	98.38%	97.96%
20/07/2025	98.89%	98.76%	98.43%
03/08/2025	99.22%	99.13%	98.90%
17/08/2025	99.56%	99.50%	99.37%
31/08/2025	99.67%	99.63%	99.53%
14/09/2025	99.78%	99.75%	99.69%
28/09/2025	100.00%	100.00%	100.00%

I cant paste the actual file, due to organisation file lockdowns, but can add similar information here if needed.

Really appreciate anyone who takes the time to lend a hand here!

[mooneyalan4]

For Future Reference. The above worked fine as long as the date 2024,3,17 existed in the list in DatesRate[Dates]
So I made a minor adjustment to the date portion only as follows:

VAR _CurrentDate =

    MINX(

        TOPN(1, ProgressRates, ABS(ProgressRates[Date] - TODAY()), ASC),

        ProgressRates[Date]

    )

[TheoC · Accepted solution]

Hi @mooneyalan4 

You can create a calculated column like the below.  Just change the date I have added in the VAR _CurrentDate to TODAY().  I used the 2024,3,17 just to ensure the formula worked.

Normally, I'd recommend you unpivot / pivot your columns in Power Query to have the A, B, C columns in a single  Category column and the percentages in a Percent column. However, given there are only three A, B, C columns, you can use SWITCH ( TRUE () with LOOKUPVALUE easily enough.

Planned Progress = 

VAR _CurrentDate = DATE ( 2024 , 3 , 17 ) // Change "DATE ( 2024 , 3 , 17 )" to "TODAY()"

VAR _PlannedPercentage = 

    SWITCH ( 
        TRUE () , 
        'ProgressRate'[ProgressRate] = "A" , LOOKUPVALUE ( DateRates[A] , DateRates[Date] , _CurrentDate ) ,
        'ProgressRate'[ProgressRate] = "B" , LOOKUPVALUE ( DateRates[B] , DateRates[Date] , _CurrentDate ) ,
        'ProgressRate'[ProgressRate] = "C" , LOOKUPVALUE ( DateRates[C] , DateRates[Date] , _CurrentDate ) , 0 
    )

RETURN
    
IF ( _PlannedPercentage = 0 , 0 , _PlannedPercentage )

Output works based on the data you provided as per below:

Hope this helps! 🙂

Theo

[mooneyalan4]

Wow, thank you! That has worked perfectly. I cannot even begin to tell you how much I appreciate your expertise and willingness to help. You may have saved my sanity here! Thank you!

[TheoC]

@mooneyalan4 it's a pleasure!  And I am truly glad that you came to the Community for help. This platform is a place with some truly gifted and talented people that, more than anything, love to help others. 

Wishing you all the best in your BI journey champion!

Theo 🙂