MAX VALUE

[Anonymous]

hi all

somebody can help me?

A	B	C	D
X	F	1	20
X	G	2	20
Y	G	BLANK	15
Y	G	1	15
Y	F	3	15
Y	F	4	15

For each value on column A, IF(B="F")&(C isnot "BLANK") , MAX(C)+D/2, Today + D/2

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please kindly try this custom column.

= if  [C]<> null and [B]="F" then List.Max(Table.Column(#"Changed Type","C"))+[D]/2 else [D]/2

List.Max(Table.Column(#"Changed Type","C")) returns the max value of column [C].

I have no idea about the "Today", if it's a column, just add them in to the formula.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

D is a date , so at the end i want to see the date.

in the example i have 2 value in the column A, ma in my database i have different value. for each value in the column a and if i have th especifics condition  IF(B="F")&(C isnot "BLANK") , i want the max date from C and for it i want to calculate the max date + related value in D/2

[ronrsnfld]

Still confused .

You keep writing about dates, but I only see whole numbers in your example.

[Anonymous]

ok do not consider " today "if was 

For each value on column A, IF(B="F")&(C isnot "BLANK") , MAX(C)+D/2, 0

what could be the formula?

[ronrsnfld]

The formula for a "Custom Column" would be:

if [C]<>"BLANK" and [B]="F" 
    then List.Max(List.Transform(#"Previous Step"[C], each try Number.From(_) otherwise null))
  + [D]/2 
     else 0

#"Previous Step" refers to the name in the Applied Steps window immediately preceding the custom column step you are working on.

The "List.Transform" is done because List.Max does not automatically ignore text entries.

[ronrsnfld]

Please clarify:

For each value on column A: But you only have two values in Column A (X and Y). Do you want to aggregate or what?

Today + D/2: What is the value of Today to be added to D/2?

[Anonymous]

in the example i have 2 value in the column A, ma in my database i have different value. for each value in the column a and if i have the specifics condition  IF(B="F")&(C isnot "BLANK") , i want the max date from C and for it i want to calculate the max date + related value in D/2