Forum Discussion

nicholas058's avatar
nicholas058
Frequent Visitor
2 years ago

Creating a Measure/Column Which Shows the Most Recent Value and one which shows the Oldest

Good Morning,    My first post on this forum, so I apologize if I'm not wording the question appropriately. However, I'm struggling to figure this out based on the existing articles I've looked at,...
nicholas058's avatar
nicholas058
Frequent Visitor
2 years ago

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. 

 

Abbreviated Data Excel 

Sample PBI 

lbendlin's avatar
lbendlin
Super User
2 years ago
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".