Need Help!-find the difference when an item changes, and summarise into another table by recent date

[db1983]

Hello,

I have a set of data which captures a timestamp anytime 5 certain fields change. 

What I want to know is when one field (replacement cost) changes, and what the date was when it changes, however its amoungst a range of other data too, additionally there are sometimes more than one change, but I only need to know the most recent change. 

I need to know this for every asset, in another summary table. 

Any suggetions ?

table should end up showing - asset id 1 = 1/1/19, and asset id 2 = 1/1/17

Asset id	Replacement cost	Time stamp	condition	Quantity
1	2000	1/01/2015	1	1
1	2000	1/01/2016	2	1
1	3000	1/01/2017	2	1
1	3000	1/01/2018	2	2
2	1000	1/01/2015	1	1
2	1000	1/01/2016	2	1
2	4000	1/01/2017	2	1
2	4000	1/01/2018	2	2
1	4000	1/01/2019	2	2

[edhans]

Try the following in Power Query. It returns this table. You said it should return the 2017 data, but that wasn't the most recent for ID#2. If it really should be 2017, what is the other critera?

EDIT: For grins, I've added a DAX expression at the bottom of this post to do the same, even though Power Query is really where this belongs most of the time.

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDAAUob6Bob6RgaGpiA2GMfqYFVgBhJCUWCMqsCckAILqAIjsAKwUnxuwKIA1Q0glgk+N2BRgOoGQ0wFlggFsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Asset id" = _t, #"Replacement cost" = _t, #"Time stamp" = _t, condition = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Asset id", Int64.Type}, {"Replacement cost", Int64.Type}, {"Time stamp", type date}, {"condition", Int64.Type}, {"Quantity", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Asset id"}, {{"All Rows", each _, type table [Asset id=number, Replacement cost=number, Time stamp=date, condition=number, Quantity=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "New Data", each Table.Max([All Rows], "Time stamp")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"New Data"}),
    #"Expanded New Data" = Table.ExpandRecordColumn(#"Removed Other Columns", "New Data", {"Asset id", "Replacement cost", "Time stamp", "condition", "Quantity"}, {"Asset id", "Replacement cost", "Time stamp", "condition", "Quantity"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded New Data",{{"Asset id", Int64.Type}, {"Replacement cost", Int64.Type}, {"Time stamp", type date}, {"condition", Int64.Type}, {"Quantity", Int64.Type}})
in
    #"Changed Type1"

Here is the DAX expression to create a table. Of course you could just use this table inside of a measure and not create an actual calculated able, but this will let you visualize what this is doing.

Summary Table = 
VAR MaxDatePerID =
    ADDCOLUMNS(
        'Sample Table',
        "MAX Date", CALCULATE(
            MAXX(
                'Sample Table',
                'Sample Table'[Time stamp]
            ),ALLEXCEPT('Sample Table','Sample Table'[Asset id])
        )
    )
VAR JustMaxRecords = 
	FILTER(
		MaxDatePerID,
		[Time stamp] = [MAX Date]
		)
RETURN
    JustMaxRecords

[db1983]

Hi,

ID#2's replacement cost didnt change in the other years though.

I only want the date to return when the replacement cost changes. if it doesnt change, then it shouldnt return anything.

It also needs to show the MOST recent date, for each asset any time the replacement cost value changes - and return the time stamp. If there are more than one change, I only want to know the most recent change. 

does that help

[edhans]

Ok. Try this M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDAAUob6Bob6RgaGpiA2GMfqYFVgBhJCUWCMqsCckAILqAIjsAKwUnxuwKIA1Q0glgk+N2BRgOoGQ0wFlggFsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Asset id" = _t, #"Replacement cost" = _t, #"Time stamp" = _t, condition = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Asset id", Int64.Type}, {"Replacement cost", Int64.Type}, {"Time stamp", type date}, {"condition", Int64.Type}, {"Quantity", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Asset id"}, {{"All Rows", each _, type table [Asset id=number, Replacement cost=number, Time stamp=date, condition=number, Quantity=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each
        let 
            varReplacementCost = Table.Max([All Rows], "Time stamp")[Replacement cost]
        in
            Table.Min(
                Table.SelectRows(
                    [All Rows], 
                    each [Replacement cost] = varReplacementCost
                    ),
                "Time stamp"
            )
        ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Other Columns", "Custom", {"Asset id", "Replacement cost", "Time stamp", "condition", "Quantity"}, {"Asset id", "Replacement cost", "Time stamp", "condition", "Quantity"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Asset id", Int64.Type}, {"Replacement cost", Int64.Type}, {"Time stamp", type date}, {"condition", Int64.Type}, {"Quantity", Int64.Type}})
in
    #"Changed Type1"

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

It turns this:

into this:

This is the magic:

let 
    varReplacementCost = Table.Max([All Rows], "Time stamp")[Replacement cost]
in
    Table.Min(
        Table.SelectRows(
            [All Rows], 
            each [Replacement cost] = varReplacementCost
            ),
        "Time stamp"
    )
)

It gets the replacement cost for the maximum date for each ID - that is what I did the first time.

Then, it returns the minimum date for those replacement costs, since everything after that would be a non-changing replacement cost.

[db1983]

Thanks - that seems to work...... now i'll just try and get this working on my actual data set - seems straight forward! 

[edhans]

Great @db1983 - post back with any problems adapting to your specific need.