Last value entered between columns(months) or by transposing last value entered from month column

[aguirre_2022]

HI Comumunity 

Its my first post , question really

Please your help 👍

I have a table with this information

{product ,january ,february, march}

a          100          12          0

b                          50        30

c            100        120      40

I need to get the last non-zero value

this value is called "quantity" with respect to month columns (January, February, March, etc).

try to solve it with a transposed base

{product, month, quantity}
a- january- 100
a- February- 12
a- March- 0
b- January- 0
b- February- 50
b- March- 30
c- January- 100
c- February- 120
c- March- 40

using lastnonblank/lastnonblankvalue but the result is sometimes wrong

could you guide me to get the following

{product ,lastvaluequantity ,lastnameornumberofmonth}
a- 12- february/2
b- 30- march/4
c- 40- march/4

also in the future I would include another parameter(column) which is year ,but at moment need the first consult.

Thanks a lot

[aguirre_2022]

it was very helpful, thank you very much

I have an additional query, if I want to take this calculated value (last val) and multiply it by a column, then I can take this value (last val) and multiply it by a column.

when using max , min,maxx or minx it gives me a different calculation

A/ last val /expected value
12 5 60
but  when use max or maxx 

A /lastval/result

12 8   96

I solved it by exporting to an auxiliary table only the last values of my product.

but I would have to make these auxiliary tables whenever I need a new calculation?

Thanks for your time

[v-rongtiep-msft · Accepted solution]

Hi @aguirre_2022 ,

Please refer to my pbix file to see if it helps you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTI0MACRRkDCQClWJ1opCcgCIlOQsDFEKBlJHYg0AQrHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [product = _t, January = _t, Febuary = _t, March = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"product", type text}, {"January", Int64.Type}, {"Febuary", Int64.Type}, {"March", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"product"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Value] = 0 then "" else [Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "value"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [value] <> null and [value] <> ""),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "month"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns1", "Index", 1, 1, Int64.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Index",{{"value", Int64.Type}})
in
    #"Changed Type1"

Then create a measure.

Measure =
VAR _1 =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[product] = SELECTEDVALUE ( 'Table'[product] )
        ),
        'Table'[Index]
    )
RETURN
    IF ( MAX ( 'Table'[Index] ) = _1, MAX ( 'Table'[value] ), BLANK () )

Finally filter the measure is bot blank.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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