Cost break down percentages per year

[bibiubiali]

Hello 😃

I need to create a cost breakdown where the price reduces yearly for 4 years, each year 50% of the previous year.

Ex: 

The benefit1 of 100$ starts in 2018

- Cost in 2018 is 50$

- Cost in 2019 is 25$

- Cost in 2020 is 12,5$

- Cost in 2021 is 6,25$

- Cost in 2022 is 0 (because the benefit only last 4 years)

Then I need to know the sum of all benefits in each year 

The data I have is the total benefit (100$ in the example) and the date on which the benefit will start counting.

[v-yadongf-msft]

Hi @bibiubiali ,

Has your problem been solved? If solved, please consider Accept it as the solution to help the other members find it more quickly.

Best regards,

Yadong Fang

[v-yadongf-msft]

Hi @bibiubiali ,

Can you explain in details and tell me what's your expected output?

Thanks for your efforts & time in advance.

Best regards,

Yadong Fang

[bibiubiali]

Hello,

I need the SUM per year of all benefits to be automatized according to new data entry (considering also the future gains in the 4 years period.

Ex. 

With this data entry:

I need the 50% breakdown yearly to be calculated (for 4 years since the beginning of the benefit), like in this example:

So I can have the SUM of total benefits in $ that I will have yearly 

(Remembering the only data in the file is the befit and the data it started or should start)

I was able to calculate it using:

2019 =

var year2019 = IF('Project savings'[Benefits Start date].[Year] = 2019, 'Project savings'[Saving]/2)

var year2018 = IF('Project savings'[Benefits Start date].[Year] = 2018, 'Project savings'[Saving]/2)

var total2019 = year2018+year2019

return

total2019

But this way I need to create one column per year every time

It is a bit complex to explain, hope I was more clear now 😅

Thanks =D

[eliasayy]

hello not really sure but mind if you try this?

2019 =
var curryear =  'Project savings'[Saving]/2
var preyear = calculate( 'Project savings'[Saving]/2),previousyear(date))
return
curryear + preyear

if no then i think you should use the new offset function

[bibiubiali]

Hello @eliasayy 

This way works the same as the solution I found, it won't automatically calculate all the new entries for upcoming years unless I create a new column, which is not ideal for me.

I will check about offset, thanks a lot!

[daXtreme · Accepted solution]

File attached.