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

Subsequent End of Life

Asset A

$1M

2018

5

2023

2028

Asset B

$2M

2019

3

2022

2025, 2028

Asset C

$1.5M

2020

5

2025

2030

Asset D

$1M

2017

6

2023

2029

 

The End of Life column is calculated, ie. End of Life = Last Replaced + Expected Life.

I have difficulty thinking of the best solution for the Subsequent End of Life and link it to the Whole Asset Replacement Cost.

 

What i need to produce is the following table - in particular the total row - and use it to produce graphs to show forecast spending in the next 10 years.

 

 

2021

2022

2023

2024

2025

2026

2027

2028

2029

2030

A

$0

 

$1M

 

 

 

 

$1M

 

 

B

$0

$2M

 

 

$2M

 

 

$2M

 

 

C

$0

 

 

 

$1.5M

 

 

 

 

$1.5M

D

$0

 

$1M

 

 

 

 

 

$1M

 

Total

0

$2M

$2M

 

$3.5M

 

 

$3M

$1M

$1.5M

 

Can someone please help. Thanks.

 

Peter 

  • 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. 

5 Replies

  • ERD's avatar
    ERD
    Community Champion
    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. 

    • PSak's avatar
      PSak
      Regular Visitor
      5 years ago

      Thanks... i applied this and it worked!!

    • Deem's avatar
      Deem
      Frequent Visitor
      3 years ago

      Hi, thanks for your solution. Your solution looks great and does exactly what I want as well. Unfortunately I can't get it working properly. I’ve rebuild the example of your solution. Hopefully you can help me with a solution? Thanks in advance.

       

      The scource data is all in numbers:

       

      The calculation gives an error when I use:

      each _<= [endyear], each _+[life],

       

      When I place fixes values then it works fine:

      each _<= 2050, each _+5,

       

       

       

       

      let
output = List.Generate(
    ()=>[firstValue],
    each _<= [endyear], each _+[life],
    each _
)
in
output

       

       

       

      The result is as follows:

       

       

      let
    Bron = Excel.Workbook(File.Contents("C:\Assetplanning.xlsx"), null, true),
    Blad1_Sheet = Bron{[Item="Blad1",Kind="Sheet"]}[Data],
    #"Headers met verhoogd niveau" = Table.PromoteHeaders(Blad1_Sheet, [PromoteAllScalars=true]),
    #"Aangepaste kolom toegevoegd1" = Table.AddColumn(#"Headers met verhoogd niveau", "firstValue", each [endyear]+[life]),
    #"Type gewijzigd" = Table.TransformColumnTypes(#"Aangepaste kolom toegevoegd1",{{"Assetcost", type number}, {"endyear", Int64.Type}, {"life", Int64.Type}, {"firstValue", Int64.Type}}),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Type gewijzigd", "LTAPlist", each let
output = List.Generate(
    ()=>[firstValue],
    each _<= [endyear], each _+[life],
    each _
)
in
output),
    #"LTAPlist uitgevouwen" = Table.ExpandListColumn(#"Aangepaste kolom toegevoegd", "LTAPlist"),
    #"Type gewijzigd1" = Table.TransformColumnTypes(#"LTAPlist uitgevouwen",{{"LTAPlist", Int64.Type}})
in
    #"Type gewijzigd1"

       

       

      When I expand to new rows I get the following error.

       

      This is the error message. 

      Expression.Error: We cannot apply field access to the type Number.

       

      When I place a fixe value for endyear the I get the following message.

      Expression.Error: We cannot apply field access to the type Number.

       

  • rfigtree's avatar
    rfigtree
    Resolver 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"

     

  • Deem's avatar
    Deem
    Frequent Visitor
    3 years ago

    Hi Peter,

     

    Does the asset life cycle calculation still work fine what was suggested in the solution? Unfortunately I can't get it work properly. Hope to hear from you. Thanks in advance.

     

    Damon