Forum Discussion
Creating a Measure/Column Which Shows the Most Recent Value and one which shows the Oldest
Hi Anonymous
This is close, but not exactly what I'm trying to accomplish.
I would like to create a measure that shows the most recent value, and another that shows the oldest value, which will allow me to have only one instance of the quote in the row.
Here is a pictured version. I've also attached this much abbreviated datasheet, as well as a dummy project file to play with.
see attached
Hey there, this was super helpful, however I'm running into another issue! It refuses to aggregate these totals. As you see in the example above, the totals are just the last line. I need to know the total sum of these changes.
In my report....
For sample data, you can still use the PBIX you provided above
you did not specify that requirement before. You will need to summarize by Line Name to get the totals.
Most Recent New Value = var a = SUMMARIZE('Table','Table'[Line Name],"md",max('Table'[CreatedDate])) var b = ADDCOLUMNS(a,"sm",var md = [md] return CALCULATE(max('Table'[NewValue]),'Table'[CreatedDate]=md)) return sumx(b,[sm])
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc9LDoMwDEXRraCMqYh/L+A1tFI7Rux/Gy2kQkmJClPr6Nqe5/C636IABA19oMEGls6ctHs+PgOEpW8bceZsrDQw2QxWA1dpdCpjrjGb8Y+R6dhJFIt74EznhiybVBqztBniFbFr47EDoi/SCgF1CReQjK11v8j20vIG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Line Name" = _t, CreatedDate = _t, NewValue = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Line Name", type text}, {"CreatedDate", type datetime}, {"NewValue", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Line Name"}, {{"Min Date", each List.Min([CreatedDate]), type nullable datetime}, {"Max Date", each List.Max([CreatedDate]), type nullable datetime}, {"Rows", each _, type table [Line Name=nullable text, CreatedDate=nullable datetime, NewValue=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "NewValue at Min Date", each Table.SelectRows([Rows],(k)=> k[CreatedDate]=[Min Date])[NewValue]{0},Int64.Type), #"Added Custom1" = Table.AddColumn(#"Added Custom", "NewValue at Max Date", each Table.SelectRows([Rows],(k)=> k[CreatedDate]=[Max Date])[NewValue]{0},Int64.Type), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Line Name", "Min Date", "Max Date", "NewValue at Min Date", "NewValue at Max Date"}) in #"Removed Other Columns"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
