topic Re: Life Cycle Calculation in DAX Commands and Tips

Life Cycle Calculation

Deem — Mon, 03 Oct 2022 07:44:48 GMT

Hi everyone,

I would like to get the following output in PowerBI. I've read a different post with a solved solution but unfortunately I can't reproduce the solution without an error. I've asked a question about it in that forum post but unfortunately nobody answers it. Therefore I've opened this new post and I hope someone can help me with a working solution. Old post: Solved: Asset Life Cycle Calculation - Microsoft Power BI Community

I need PowerBI to create the following table. Every AssetNo has to be replaced after it's expected Life for a fixed amount of costs. The next step would be to integrate a index so the replacements cost wil be indexed every year.

The calculated tabled is based on the following dataset. For example every 5 year AssetNo A has to be replaced for a value of € 1.000,--.

AssetNo	endyear	life	Assetcost
A	2018	5	€ 1.000
B	2019	3	€ 2.000
C	2020	5	€ 1.500
D	2017	6	€ 1.000

I've build the following function that will provide a list of replacement years for each last replacement year. This gives an error.

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

This function creates a list but comes withe an error when I expand the list to new rows.

When I expand to new rows I get the following error. "Expression.Error: We cannot apply field access to the type Number."

When I place a fixed value for endyear (2050) and life (5) then it works fine. I can live wit a fixed value but the value live comes from the dataset.

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

The output is with tunnecessary columns deleted is:

Does somebody know a solotion for this problem or a different method in PowerBI. Thanks in advance.

Damon

https://1drv.ms/u/s!AvqOdziejqaXluYnp2YAh2Bpk5SYqA?e=oALPaS

Re: Life Cycle Calculation

Greg_Deckler — Sun, 02 Oct 2022 20:47:10 GMT

Deem Here is a DAX solution. Basically Blowout! PBIX is attached below sig.

Blowout! = VAR __Table = GENERATE( 'Table', VAR __MinYear = [endyear] VAR __increment = [life] RETURN GENERATESERIES(__MinYear,__MinYear + __increment * 3,__increment) ) RETURN __Table

Re: Life Cycle Calculation

v-yueyunzh-msft — Mon, 03 Oct 2022 06:31:24 GMT

Hi, Deem

I test it with your data, Here are the steps you can refer to :

(1)This is my test data:

(2)We can Add a custom colimn in Power BI Query Editor:

(x)=> List.Generate(()=>x[Last Replaced],(y)=>y<=Number.From(x[End of Year]) ,(y)=>y+Number.From(x[Expected Life]) )

(3)Then we can click "Expand to Mew Rows".

(3)Then we aplly the data to the Power BI Desktop and we can put the field in the visual and we will meet your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Life Cycle Calculation

Deem — Mon, 03 Oct 2022 07:07:04 GMT

Hi v-yueyunzh-msft,

Thank for your quick response. It looks great. Unfortunately I get a new column with function. And I can't open your Pbix because is searching for a Excelfile.

I will attach my original dataset in the opneing post.

Re: Life Cycle Calculation

v-yueyunzh-msft — Mon, 03 Oct 2022 07:20:02 GMT

Hi, Deem

You can try to delete the "each" in the enter box :

Then you can get the list .

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Life Cycle Calculation

Deem — Mon, 03 Oct 2022 07:49:40 GMT

Hi v-yueyunzh-msft ,

Sorry but removing each doesn't work. At the bottom of my original post I've added a Onedrive downloadlink of my pbix . Can you pleas have a look at it. Thanks.

Re: Life Cycle Calculation

Deem — Mon, 03 Oct 2022 08:07:38 GMT

HI Greg_Deckler ,

I wil try this solution later this day on my big dataset. I've tried it and it works on the test dataset. I will let you know if it works fine. Thanks!

Re: Life Cycle Calculation

v-yueyunzh-msft — Mon, 03 Oct 2022 08:26:55 GMT

Hi , Deem

I download your .pbix file , For your problem, Here are my answers:

(1)At present, the value you modified is correct, and you can return the correct table, where 2050 means that the year of the generation stops in 2050, and the deadline for the generation is not specified in your table, so you need to replace the value with 2050.

(2)For the [life] field , you can edit the M language in "Aangepaste kolom toegevoegd" step:

= Table.AddColumn(#"Type gewijzigd", "Aangepast", (x)=> List.Generate(()=>x[endyear]+x[life],(y)=> y<=2050 , (y)=>y+x[life] ) )

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Life Cycle Calculation

Deem — Mon, 03 Oct 2022 18:05:11 GMT

Hi Greg_Deckler , your solutions works also fine. The only thing is that it generates much more new rows because of the incrementfactor then the solution of v-yueyunzh-msft. So I have chosen the solution of v-yueyunzh-msft for my case.

Many thanks for your quick replies!!💪💪