topic Re: budget calculation for categories along 4 years in DAX Commands and Tips

budget calculation for categories along 4 years

pedroccamaraDBI — Wed, 22 Dec 2021 08:44:19 GMT

Hello all
I would like to do a budget table that has only 2 columns : budgets and month & Year.
But for year 2018, i want to add 20%, for year 2019 50%, year 2020 70% and so on.
I came up with this but i don' t know how to go further.

Budget =

ADDCOLUMNS(

VALUES( 'Tab Dates'[Month & Year]),

"Budgets", CALCULATE( [Net Sales],'Tab Dates'[Year] = 2018) *1.2)

Can someone help me?
Thanks a lot

Re: budget calculation for categories along 4 years

amitchandak — Wed, 22 Dec 2021 09:20:21 GMT

pedroccamaraDBI , I think what you need like

ADDCOLUMNS(
generate(
Summarize(Table, Table[Year], Table[Month], "_sales", calculate(Sum(Table[Net Sales]), FIlter(Table, Table[Year] =2020))),
union(row("_year", 2019),row("_year", 2020),row("_year", 2018))
), "Sales", Switch(true(), _year =2019, _sales *1.2, _year =2020, _sales *1.5, _sales))

Re: budget calculation for categories along 4 years

pedroccamaraDBI — Wed, 22 Dec 2021 12:16:43 GMT

Hello amitchandak
Thank you very much for you reply. I'm not sure if this is the expected result. Could i have it all budgets in the same column?

Thanks a lot

Re: budget calculation for categories along 4 years

VahidDM — Wed, 22 Dec 2021 12:36:22 GMT

Hi pedroccamaraDBI

Try this:

Budget = VAR _A = SWITCH( TRUE(), 'Tab Dates'[Year] = 2018, 1.2, 'Tab Dates'[Year] = 2019, 1.5, 'Tab Dates'[Year] = 2020, 1.7 ) RETURN ADDCOLUMNS( VALUES( 'Tab Dates'[Month & Year] ), "Budgets", [Net Sales] * _A )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: budget calculation for categories along 4 years

VahidDM — Wed, 22 Dec 2021 12:36:27 GMT

Hi pedroccamaraDBI

Try this:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: budget calculation for categories along 4 years

pedroccamaraDBI — Wed, 22 Dec 2021 13:07:25 GMT

Hello VahidDM
Thank you very much for helping me.
I can't enter tab dates [Year] = 2018, 1.2 and so on
I've tried using calculate here but it won't let me. Can you help me?

Re: budget calculation for categories along 4 years

VahidDM — Wed, 22 Dec 2021 13:22:05 GMT

pedroccamaraDBI
Can you share a samle of your PBIX file? after removing sensetive data

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: budget calculation for categories along 4 years

VahidDM — Wed, 22 Dec 2021 23:22:04 GMT

pedroccamaraDBI

Can you share a sample of your data in text format and with all value and columns?
and expected result as well.

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: budget calculation for categories along 4 years

pedroccamaraDBI — Thu, 23 Dec 2021 10:08:59 GMT

Hello VahidDM
I'm attaching and excel file with my data.
Let me remind what i need:
I want to know the budget for any column i choose in the fact table, product, family, sub family or zone or any date, as long as if the sales of the year is:
2018 then multiply sales of 2018 by 1
2019 then multiply sales of 2018 by 1.2
2020 then multiply sales of 2019 by 1.3
2021 then multiply sales of 2020 by 1.4
2022 then multiply sales of 2021 by 1.5
I hope it's clear.
Thanks a lot
https://drive.google.com/file/d/18mxzktGBs_lNIi7fCu8qF4uL9M2l7YMb/view?usp=sharing

Re: budget calculation for categories along 4 years

Anonymous — Tue, 28 Dec 2021 08:59:27 GMT

Hi pedroccamaraDBI ,

If you only have the value of 2018 in the table, you will need to create multiple columns for each year.

Best Regards,

Jay