Forum Discussion
Calculating Max Date in a Measure
Hello,
I am currently picking my brain apart trying to figure out where I am going wrong. All I am simply trying to achieve is the Max Date that corresponds with a certain investment, that is less than or equal to the variable date I have set.
Currently have the below formula:
Anonymous Thanks foodd I missed the part of 2018, redo the measure like this but if you just want max date without any filter context then PQ is the way to go but keep in mind grouping is a very expensive transformation and can slow everything if it is a large table.
Max Date = CALCULATE ( MAX ( Table[Date] ), Table[Date] <= DATE ( 2018, 12, 31 ) )
4 Replies
Anonymous add a measure and use Investment and this measure in a table visual:
Max Date = MAX ( Table[Date] )Anonymous Thanks foodd I missed the part of 2018, redo the measure like this but if you just want max date without any filter context then PQ is the way to go but keep in mind grouping is a very expensive transformation and can slow everything if it is a large table.
Max Date = CALCULATE ( MAX ( Table[Date] ), Table[Date] <= DATE ( 2018, 12, 31 ) )Anonymous, the DAX Solution parry2k supplied is quite fine. Thought you would like a filtering solution in powerquery-m for variety sake:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQM8FHSUTIw1Dc01TcyMLRQitVBiJrrGxogRIMDIkGCJvoGFghBn1CfUJCopT4QoRtgiGosxACgkWBTjQyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Investment = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <= #date(2018, 12, 31)),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"Investment"}, {{"Latest Date", each List.Max([Date]), type date}})
in
#"Grouped Rows"
