budget calculation for categories along 4 years

[pedroccamaraDBI]

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

[Anonymous]

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

[VahidDM]

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/

[VahidDM]

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/

[pedroccamaraDBI]

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?

[VahidDM]

@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/

[VahidDM]

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

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

[pedroccamaraDBI]

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 

[amitchandak]

@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))

[pedroccamaraDBI]

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