How to return value based on MAX in separate column

[Merchant23]

Hello,

I am hoping to find a formula to help me SUM my on hand units, based on the most recent (greatest) week number. This is part of a larger formula determining which data set to use, so the simpler the better!

In this case I'd want to Sum the 'Wkly_Inv_qty' based on the Max value in 'Week Number'.
So the greatest week is 14, and a Sum on the qty by week 14 would be 423 (144+85+23+171)

Thank you for helping!

[v-yalanwu-msft · Accepted solution]

Hi, @Merchant23 ;

In power query.

= Table.AddColumn(#"Changed Type", "Custom", each if [Week_ Num]= List.Max( #"Changed Type"[Week_ Num]) then 1 else null)

= Table.AddColumn(#"Added Custom", "Custom.1", each List.Sum(Table.SelectRows(#"Added Custom",each [Custom]=1)[Wky]))

the final show:

The M language.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZC7DcUwCAB3oU7BzwHPYmX/NV4MkaF7HWfJd4i1AOECg+dawP6OHqPpfp4x3zzfmTBA5thA+cF4AweQB0iC6gYNqAKeABOfgKBXgFkqQK4tQKMCPv74tfaXsmvbXu5yC5Y6F0g113GOWqzdhrqcuh2t9Jzw3cboCzw/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Wky = _t, #"Week_ Num" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Wky", Int64.Type}, {"Week_ Num", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Week_ Num]= List.Max( #"Changed Type"[Week_ Num]) then 1 else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Sum(Table.SelectRows(#"Added Custom",each [Custom]=1)[Wky])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"

In dax.you could create the measure.

dax = CALCULATE(SUM('Table'[Wky]),FILTER(ALL('Table'),[Week_ Num]=CALCULATE(MAX('Table'[Week_ Num]),ALLSELECTED('Table'))))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rohit_singh]

Hi @Merchant23 ,

Not sure what exactly your expected output is, but you can try something like this :

I've only taken the first 2 weeks in the sample data

Sum Max Qty =

var _maxwk = CALCULATE(MAX(Inv[Week_Num]),ALL(INV))

var _sum =

CALCULATE(

SUM(Inv[Wkly_Inv_qty]),

FILTER(allselected(INV), Inv[Week_Num] = _maxwk)

)

RETURN

_sum

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂