Need help creating a for loop to calculate variable for each row

[Anonymous]

I want to calculate a variable for each year, based on the previous year and some user input (perhaps using whatif parameters).

Input parameters:

Percentage of women today (e.g. 12%)

Percentage of women desired (e.g. 50%)

Efflux of personnel yearly (e.g. 10%)

Current hiring policy (e.g. 70% women, 30% men)

The resulting column should look like this:

Year	% women
2019	12.00000
2020	17.80000
2021	23.02000
2022	27.71800
2023	31.94620
2024	35.75158

If it helps, this is the Python code to do it (may not be pretty but it works):

amount_w = int(input('What is the current percentage of women?'))

efflux = int(input('What is the efflux per year?'))/100
replace_w =int(input('What is the current hiring of women?'))
goal=int(input('What is the desired percentage of women?'))

replacement['Year']=[2019,2020,2021,2022,2023,2024]
replacement['Number of women'] = amount_w
replacement['Goal']=goal

def calculate(amount_w):
amount_w = (amount_w * (1 - efflux)) + ((100 * efflux)*replace_w)
return amount_w

for i in range(1, len(replacement)):
replacement.loc[i, 'Number of women'] = calculate(replacement.loc[i-1, 'Number of women'])

Can I do this in DAX? Or using the Python script in PowerBI? Please let me know if I need to add more info, it's my first time posting a question here.

[v-xicai]

Hi @Anonymous ,

Could you please clarify what's the logic to get your desired output using current data sample?

Best Regards,

Amy

[Anonymous]

Sure @v-xicai!
It's supposed to be a calculator for how long it takes to reach a 'diversity goal' such as the amount of women in leadership positions.

I work with a theoretical 100 people. Every year the same amount of people leave and are replaced. First you ask for input:
How many women are currently in leadership positions? amount_w

How many people (not just women) leave each year? efflux

How many new hires are women? replace_w

Then, I want to make a table where each row is a year. For each year, I want to calculate the amount of women:

2019 > amount of women is the starting amount amount_w

2020 > amount_w = (amount_w[2019] * (1 - efflux)) + ((100 * efflux)*replace_w)
2021 > amount_w = (amount_w[2020] * (1 - efflux)) + ((100 * efflux)*replace_w)

Etc.

To give an example:

amount_w =12

efflux = 10

replace_w = 70

Year	Calculation	Result
2019		12.00000
2020	12 * (1-0.1) + (100*10)*.7	17.80000

I hope this clears things up!

[Anonymous]

To me it looks like an algorithm that calculates N values (based on year).  It's a simple formula but the question is where is your data?

You should create a table with a row per year, then another with all possible values of amount_w, then another with all possible values of efflux and another with replace_w

These are your slicers in your page where the person can select their values and will be visible in your formula with a code like amount_w=selectedvalue(TableAmount[amount])

Then your DAX measure it's a little problematic because it's recursive. Amount_w[2020] depends on amount_w[2019], therefore I would use a calculated column rather than a measure.

Finally, don't try porting a python code, iterative, to DAX code which is functional. The logic is completely different, there are no "for" loops. Something that is similar to a for loop are X functions (like SUMX, MINX etc) but, again, they're not for loops. 

So my suggestion is to try to learn basics of PowerBI and DAX and then attempt a translation. Unfortunately DAX can't be learned "one step at a time", you need to have strong foundations to make simple use of it.

[Anonymous]

>>You should create a table with a row per year, then another with all possible values of amount_w, then another with all possible values of efflux and another with replace_w

Are you saying I should make a table where I pre-calculate all possible outcomes based on all possible input values? Is there no way to calculate on the fly?

[Anonymous]

No, i'm saying that if you want amount_w in a filter (so you want the person to be able to "choose a value"), those values (all of them) must be in a column if it's scalar. If it's a float, it can be just a column