topic Re: Possible to duplicate LOGEST() function in powerBI in DAX Commands and Tips

Possible to duplicate LOGEST() function in powerBI

Anonymous — Sun, 23 Feb 2020 23:38:14 GMT

Hi, all - first time posting, relatively new to using PowerBI for anything other than data pulls.

I'd like to define a measure as follows:

1. define x_1, x_2, ... x_5 and y_1, y_2, ... y_5 as VAR

2. return the same output that I would get if I inputted these as the known x and known y in LOGEST() in excel--i.e., the exponential rate of growth.

Is this possible? Can't seem to find any documentation on it.

Thanks in advance for your help 🙂

Re: Possible to duplicate LOGEST() function in powerBI

Anonymous — Mon, 24 Feb 2020 10:53:22 GMT

Well... Where do you take your VAR's from? Please note that VAR's in DAX are not something that you can pass in from outside to a measure. VAR's are there only to make a reference to something so that it's calculated once. Measures work on columns of tables and they change their values when filters on other columns change.

Not sure what you really want to achieve...

Best
D

Re: Possible to duplicate LOGEST() function in powerBI

Anonymous — Mon, 24 Feb 2020 15:03:06 GMT

Thanks for your response! I'm aware of all this, sorry if I wasn't clear. My desired output is the same output as the LOGEST() function in excel, i.e. the growth rate of an exponential curve fitted to an inputted list of known x and y values. I would be defining the VAR x_1 etc values within this measure purely for the purpose of calculating this output.

EDIT - in answer to where I get my VAR values from, I have one measure [COST] for which I have data over 5 years. I would set VAR y_1 equal to [COST] filtered to the first year, y_2 equal to [COST] filtered to the second year, etc. I have successfully done this to calculate other measure, eg year-over year change, linear trend, etc. The x_1, x_2, etc values would always be equal to 1,2,3,4,5. What I ultimately want to see is, for any given cut of the data, how the cost has been trending year over year using an exponential fit.

As a simplified example, if I input the following known x and y values to the LOGEST() function in excel, I get a growth rate of 2.402. What I'm wondering is if there's anything I can do in PowerBI with the same information to get the same output, or if I should just export my data to excel and work from there.

Growth =

VAR x_1 = 1

VAR x_2 = 2

VAR x_3 = 3

VAR x_4 = 4

VAR x_5 = 5

VAR y_1 = 5

VAR y_2 = 30

VAR y_3 = 130

VAR y_4 = 120

VAR y_5 = 200

RETURN ???

Re: Possible to duplicate LOGEST() function in powerBI

Anonymous — Mon, 24 Feb 2020 15:44:08 GMT

Hi there.

Mate, it looks like you'd like to fit to your data points a curve of this shape:

y(x) = b*m^x

where b, m are unknown constants.

This problem becomes a standard linear regression problem when you transform both sides with a logarithm:

log(y(x)) = log(b*m^x) log(y(x)) = log(b) + log(m) * x

Now you can treat log(y(x)) as your new y-value and put log(b) = C, log(m) = B. Hence, you want to estimate B, C. Once you have these, you can do the reverse to get b and m.

Your new linear regression equation is y' = Bx + C. Now just calculate the estimates of B and C by the standard formulas for linear regression in one variable.

Best

Re: Possible to duplicate LOGEST() function in powerBI

Anonymous — Mon, 24 Feb 2020 23:32:26 GMT

This is super helpful, thank you!