cancel
Showing results 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.

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.

1 ACCEPTED SOLUTION
Solution Sage

File attached.

6 REPLIES 6
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,

Community Support

Hi @bibiubiali ,

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

Best regards,

Frequent Visitor

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

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

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

Frequent Visitor

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!

Solution Sage

File attached.

Announcements

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

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

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