topic Re: Need to find a latest value for Unit across Dates in DAX Commands and Tips

Need to find a latest value for Unit across Dates

Anonymous — Wed, 29 Sep 2021 17:28:43 GMT

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

Re: Need to find a latest value for Unit across Dates

ERD — Wed, 29 Sep 2021 16:02:52 GMT

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.

Re: Need to find a latest value for Unit across Dates

ERD — Wed, 29 Sep 2021 17:10:48 GMT

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.

Re: Need to find a latest value for Unit across Dates

Anonymous — Wed, 29 Sep 2021 17:26:22 GMT

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.

Re: Need to find a latest value for Unit across Dates

Anonymous — Wed, 29 Sep 2021 17:30:30 GMT

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

Re: Need to find a latest value for Unit across Dates

ERD — Thu, 30 Sep 2021 05:48:27 GMT

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.