topic Re: Cost break down percentages per year in DAX Commands and Tips

Cost break down percentages per year

bibiubiali — Thu, 20 Oct 2022 12:40:39 GMT

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.

Re: Cost break down percentages per year

v-yadongf-msft — Fri, 21 Oct 2022 03:11:24 GMT

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

Re: Cost break down percentages per year

bibiubiali — Fri, 21 Oct 2022 08:11:57 GMT

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

Re: Cost break down percentages per year

Anonymous — Fri, 21 Oct 2022 10:32:23 GMT

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

Re: Cost break down percentages per year

bibiubiali — Mon, 24 Oct 2022 08:30:17 GMT

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!

Re: Cost break down percentages per year

daXtreme — Mon, 24 Oct 2022 11:46:09 GMT

File attached.

Re: Cost break down percentages per year

v-yadongf-msft — Wed, 26 Oct 2022 05:31:54 GMT

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