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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
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!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.