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

Question

Hello,&nbsp;I have a set of data which captures a timestamp anytime 5 certain fields change.&nbsp;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.&nbsp;I need to know this for every asset, in another summary table.&nbsp;Any suggetions ?table should end up showing - asset id 1 = 1/1/19, and asset id 2 = 1/1/17&nbsp;Asset idReplacement costTime stampconditionQuantity120001/01/201511120001/01/201621130001/01/201721130001/01/201822210001/01/201511210001/01/201621240001/01/201721240001/01/201822140001/01/201922

edhans · Answer

Great&nbsp;db1983&nbsp;- post back with any problems adapting to your specific need.

edhans · Answer

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:&nbsp;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.
&nbsp;

&nbsp;
1) In Power Query, select New Source, then Blank Query2) On the Home ribbon, select "Advanced Editor" button3) Remove everything you see, then paste the M code I've given you in that box.4) Press Done
&nbsp;
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"
&nbsp;
&nbsp;
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
&nbsp;

db1983 · Answer

Hi,&nbsp;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.&nbsp;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.&nbsp;&nbsp;does that help

edhans · Answer

Ok. Try this M code:
&nbsp;
&nbsp;
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"
&nbsp;
&nbsp;
1) In Power Query, select New Source, then Blank Query2) On the Home ribbon, select "Advanced Editor" button3) Remove everything you see, then paste the M code I've given you in that box.4) Press Done
&nbsp;
It turns this:

into this:

&nbsp;
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&nbsp;minimum&nbsp;date for those replacement costs, since everything after that would be a non-changing replacement cost.

db1983 · Answer

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

Forum Discussion

5 Replies

Related content