Need to find a latest value for Unit across Dates

[Anonymous]

Hi guys, I have data similar to below table where I need to create a measure to get the latest Unit Size for a given Unit Number (Unit 1, Unit2, Unit3) across the dates and output should be according to the results as highlighted below. Please can someone help me with the DAX command.

Sample Data:
              01-01-20      02-01-20     03-01-20
              Unit Size       Unit Size      Unit Size
Unit 1     25                15
Unit 2     50                40
Unit 3     10

[ERD]

@Anonymous ,

I'd suggest you to first prepare your data in Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WOrRASUcpNC+zRCE4syqVAC9WJxrCNwRKGJnCCIS4EZBnagAkzAxQxI2BPEOQENhIpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"01-01-20" = _t, #"02-01-20 " = _t, #"03-01-20" = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"(blank)"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([#"(blank)"] <> " ")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Value", type number}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Attribute", type date}}, "en-CH")
in
    #"Changed Type with Locale"

Then you can try this measure:

latestValue = 
VAR latestDate = MAXX ( FILTER ( 'Table', 'Table'[Value] <> BLANK () ), 'Table'[Date] )
RETURN
    CALCULATE ( MAX ( 'Table'[Value] ), 'Table'[Date] = latestDate )

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

[Anonymous]

Thanks @ERD .

The following DAX will return the MAX of the Unit Size right? Ex- For Unit 2 and date - 02-01-20, its giving 60.
What I need is the Latest value, if for same Unit 2, the Unit Size is 40 on date - 02-01-20 then Latest Unit Size in Unit 2 should return 40.

[Anonymous]

@ERD , I have updated the post and sample data to be clear on the requirements.

[ERD]

@Anonymous ,

the code will return the latest value, not the biggest one:

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

[ERD]

@Anonymous ,

Please, provide sample data as text, use the table tool in the editing bar.

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