Asset Life Cycle Calculation

[PSak]

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 

[Deem]

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

[ERD · Accepted solution]

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

[Deem]

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.

[PSak]

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

[rfigtree]

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"