Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Cost break down percentages per year

Hello 😃


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



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.


Community Support
Community Support

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

Community Support
Community Support

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



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.



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

Impactful Individual
Impactful Individual

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))
curryear + preyear


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

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!

File attached.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors