Forum Discussion

db1983's avatar
db1983
New Member
6 years ago

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

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 idReplacement costTime stampconditionQuantity
120001/01/201511
120001/01/201621
130001/01/201721
130001/01/201822
210001/01/201511
210001/01/201621
240001/01/201721
240001/01/201822
140001/01/201922
Need Help
Tips and Tricks

5 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      db1983
      New Member
      6 years ago

      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's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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.