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.

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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'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.