topic Re: Asset Life Cycle Calculation in DAX Commands and Tips

Asset Life Cycle Calculation

PSak — Wed, 31 Mar 2021 00:13:35 GMT

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

Re: Asset Life Cycle Calculation

rfigtree — Wed, 31 Mar 2021 08:26:56 GMT

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"

Re: Asset Life Cycle Calculation

ERD — Wed, 31 Mar 2021 10:23:59 GMT

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.

Re: Asset Life Cycle Calculation

PSak — Thu, 01 Apr 2021 00:03:42 GMT

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

Re: Asset Life Cycle Calculation

Deem — Sun, 02 Oct 2022 19:38:16 GMT

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.

Re: Asset Life Cycle Calculation

Deem — Fri, 23 Sep 2022 19:09:08 GMT

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