Extract Maximum value

[OscarSuarez10]

Hello I want to extract the maximum year and maximum value < 0 , of the following table usign power bi, can you help me?

YEAR	CUMULATIVE CASHFLOW	Max VALUE	Max Year
1	-284563008
2	-265282108
3	-254115908	-254115908	3
1	-284556992
2	-265323292
3	-254154892	-254154892	3
1	-231575008
2	-210629208
3	-200337808	-200337808	3
1	-266324992
2	-246446192
3	-235308992	-235308992	3

[dax · Accepted solution]

Hi OscarSuarez10,

You could create column like below by Mcode

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY3BCcAwDMR28bsF2+dznVlC91+jJQ0Ff4WE5hSTQ06vYEK15D6m+EJJL7eNsBDDjGOjP2SO4S2Ewzf6Q0Zt9IUwXuxH03yzdlQFrmrHTHj0Y2REWjuC0FrW/QA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [YEAR = _t, #"CUMULATIVE CASHFLOW" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"YEAR", Int64.Type}, {"CUMULATIVE CASHFLOW", Int64.Type}}),
    #"Added Custom" = Table.AddColumn( Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), "Custom group", each Number.RoundUp([Index]/3)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

Then use measure like below

Measure =
VAR maxt =
    MAXX (
        SUMMARIZE (
            'Table (2)',
            'Table (2)'[Custom group],
            "sum", CALCULATE (
                MAX ( 'Table (2)'[CUMULATIVE CASHFLOW] ),
                ALLEXCEPT ( 'Table (2)', 'Table (2)'[Custom group] )
            )
        ),
        [sum]
    )
RETURN
    IF (
        MIN ( 'Table (2)'[CUMULATIVE CASHFLOW] ) = maxt,
        MIN ( 'Table (2)'[YEAR] ),
        ""
    )

Measure 2 =
VAR maxt =
    MAXX (
        SUMMARIZE (
            'Table (2)',
            'Table (2)'[Custom group],
            "sum", CALCULATE (
                MAX ( 'Table (2)'[CUMULATIVE CASHFLOW] ),
                ALLEXCEPT ( 'Table (2)', 'Table (2)'[Custom group] )
            )
        ),
        [sum]
    )
RETURN
    IF ( MIN ( 'Table (2)'[CUMULATIVE CASHFLOW] ) = maxt, maxt, "" )

You could refer to my sample

Best Regards,
Zoe Zhi

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