Life Cycle Calculation

[Deem]

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 

[v-yueyunzh-msft]

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

[Deem]

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.

[v-yueyunzh-msft]

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

[Deem]

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.

[Greg_Deckler]

@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

[Deem]

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!

[Deem]

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!!💪💪

[v-yueyunzh-msft · Accepted solution]

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