Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

MAX VALUE

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...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

ronrsnfld's avatar
ronrsnfld
Super User
4 years ago

Still confused .

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    ronrsnfld
    Super User
    4 years ago

    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.