Forum Discussion

PSak's avatar
PSak
Regular Visitor
5 years ago
Solved

Asset Life Cycle Calculation

Hi everyone,   I have the following dataset that shows basic information of several assets:   Asset No Whole asset replacement cost Last Replaced Expected Life End of Life Subs...
  • ERD's avatar
    ERD
    5 years ago

    PSak ,

    I think it might be easier to have all Expected Life End years in one column.

    I assumed that you need a chart with a forecast for the years 2021-2030. So my first thought was about the loop (while year <=2030, count next replacement year). I did it with Power Query as well, but using a bit different approach:

    1. Create a function that will provide a list of replacement years for each Last Replaced year

     

    (endyear as number, life as number, maxYear as number, optional results as list) =>
let
firstValue = endyear+life,
output = List.Generate(
    ()=>firstValue,
    each _<= maxYear, each _+life,
    each _
)
in
output

     

    2. Optional: You can duplicate your query to have only 3 columns as a result (assuming your initial query has more columns, etc.).

    3. Add column - Invoke the function from the previous step:

    • New column name - provide the name you prefer
    • Function query - name of the function from step 1
    • endyear - Last Replaced column
    • life - Expected Life column
    • maxYear - put the max year for the chart. In provided example - 2030

    As a result you will get a list of years for each row in the new column.

    4. Use the expand icon in the upper-right corner of the column label and select Expand to New Rows.

    5. Optional: delete all unnecessary columns. 

rfigtree's avatar
rfigtree
Icon for Resolver III rankResolver III
5 years ago

Power Query; probably can be done in a fancier way than I did it but works.

see m code below.

 

 

let
    Source = Excel.CurrentWorkbook(){[Name="tblAsset"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Asset No", type text}, {"RepCost($M)", type number}, {"Last Replaced", Int64.Type}, {"Expected Life", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each tblYear),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"year"}, {"year"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"year", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Mod", each Number.Mod([year] - [Last Replaced],[Expected Life]), type number),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Quotient", each (([year]-[Last Replaced]) / [Expected Life]), type number),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Replacement", each if [Mod] <> 0 then 0 else 
if [Quotient] < 1 then 0 else [#"RepCost($M)"], type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"RepCost($M)", "Last Replaced", "Expected Life", "Mod", "Quotient"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"year", type text}}, "en-AU"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"year", type text}}, "en-AU")[year]), "year", "Replacement", List.Sum)
in
    #"Pivoted Column"