Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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

6 REPLIES 6
v-yadongf-msft
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

v-yadongf-msft
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

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:

bibiubiali_0-1666338914361.png

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

bibiubiali_1-1666339012479.png

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

bibiubiali_2-1666339100103.png

 

(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

Anonymous
Not applicable

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

Hello @Anonymous 

 

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

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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