help required with complex (well, i think so) statement in power query

[ajm]

Can anyone help with this formula. in excel i can manage it, but in M.... that's a different matter entirely. i am setting traffic lights to indicate progress on a report.

Col A	Col B	Col C	Col D	Col E	Col F	Col G	Col H	Today
Contract	Start Date for Next Activity	End Date for Next Activity	% Complete	Duration	Calculated Days	Progress Date	Indicator	26/04/2024
BRD-STP-U-0004	18/12/2020	7/07/2023	60	931	558.6	29/06/2022	RED
UW001354	3/04/2023	31/07/2023	65	119	77.35	19/06/2023	RED
UW001090	9/04/2024	30/04/2024	20	21	4.2	13/04/2024	AMBER
UW000457	3/11/2023	1/03/2024		119	0	3/11/2023	RED
UW000459	3/11/2023	1/03/2024	75	119	89.25	31/01/2024	RED
UW000723	3/11/2023	1/03/2024	75	119	89.25	31/01/2024	RED
BRD-STP-R-0048	7/07/2023	22/03/2024	70	259	181.3	4/01/2024	RED
UW001457	8/03/2024	29/03/2024	100	21	21	29/03/2024	GREEN

the formula in excel for the Indicator colour is:

=IF(D2=100,"GREEN",IF(AND(OR(D2=0,D2=""),TODAY()>B2),"RED",IF((TODAY()-(B2+((D2/100)*E2)))>14,"RED",IF(AND((TODAY()-(B2+((D2/100)*E2)))<=14,(TODAY()-(B2+((D2/100)*E2)))>=0),"AMBER","GREEN"))))

Col D contains the % Completion of each project. So, if its 100%, excellent. GREEN. if its 0 or Null, AND we are past the start date, Bad RED.

 

[ajm]

The rest of the formula compares the progress date (start date plus (% completion x duration of project)) to today's date and if its inside 14 days over or under, Amber, greater than 14 days, RED, everything else Green.

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"Start Date for Next Activity", type date}, {"End Date for Next Activity", type date}}),
    today = Date.From(DateTime.LocalNow()),
    Result = Table.AddColumn(ChangeType, "Indicator color (as of " & Text.From(today) & ")", each let 
                startdate = [Start Date for Next Activity],
                enddate = [End Date for Next Activity],
                progress = if [#"% Complete"] = null then 0 else [#"% Complete"]/100,
                plannedduration = enddate - startdate,
                targetdate = Date.AddDays(startdate, Number.Round(Duration.Days(plannedduration)*progress)),
                daysfromtarget = Duration.Days(targetdate - today)
            in 
                if progress = 1 then "GREEN" else 
                if today > enddate then "RED" else 
                if Number.Abs(daysfromtarget) <= 14 then "AMBER" else 
                if daysfromtarget > 14 then "GREEN" else "RED") 
in
    Result

Book1
	A	B	C	D	E	F
1	Table4
2	Contract	Start Date for Next Activity	End Date for Next Activity	% Complete
3	BRD-STP-U-0004	12/18/2020	7/7/2023	60
4	UW001354	4/3/2023	7/31/2023	65
5	UW001090	4/9/2024	4/30/2024	20
6	UW000457	11/3/2023	3/1/2024	0
7	UW000459	11/3/2023	3/1/2024	75
8	UW000723	11/3/2023	3/1/2024	75
9	BRD-STP-R-0048	7/7/2023	3/22/2024	70
10	UW001457	3/8/2024	3/29/2024	100
11
12
13	Query Output
14	Contract	Start Date for Next Activity	End Date for Next Activity	% Complete	Indicator color (as of 4/26/2024)
15	BRD-STP-U-0004	12/18/2020	7/7/2023	60	RED
16	UW001354	4/3/2023	7/31/2023	65	RED
17	UW001090	4/9/2024	4/30/2024	20	AMBER
18	UW000457	11/3/2023	3/1/2024	0	RED
19	UW000459	11/3/2023	3/1/2024	75	RED
20	UW000723	11/3/2023	3/1/2024	75	RED
21	BRD-STP-R-0048	7/7/2023	3/22/2024	70	RED
22	UW001457	3/8/2024	3/29/2024	100	GREEN
23
Sheet4

 

[ajm]

i'm liking this one @JGordon11 . Many thanks