'An Argument of Function Ln has wrong data type or result is too small or too large'

[hwoehler]

Hello everybody,
I want to calculate the logarithm of a division (close/prev-closing). The following columns are relevant for this: Date, Close, PreviousClose, PreviousDate. The general change in % can be calculated using the formula "(Close / Prev_Closing) -1". In my case I would now like to calculate the logarithm. In Excel, the formula would be: ln(Close / Prev_Closing). The formula in Power BI "DailyChangeLN = ln (divide (Fact_Stocks [Close]; Fact_Stocks [Prev_Closing]))" unfortunately gives me ERROR with the error: 'An Argument of Function Ln has wrong data type or result is too small or too large '. Why is that?
Best regards, hwoehler

[amitchandak]

Check both are of decimal data type. Try new column like

new column = divide(table[Close] ,table[Prev_Closing]) -1

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[hwoehler]

Hi @amitchandak,

thanks for your quick reply. Unfortunately it doesn't work. Both Columns (Close and Prev_Closing) are of the decimal number data type. Maybe you have time to have a closer look at my file: https://we.tl/t-wizGGyNNix
Regards, hwoehler

[amitchandak]

I added this one and it worked

DailyChangeLN = DIVIDE(Fact_Stocks[Close],Fact_Stocks[Prev_Closing])-1

Refer :https://www.dropbox.com/s/fn97mbhbxzxzpbr/Korrelation.pbix?dl=0

[hwoehler]

Hi @amitchandak,

you're right. This is the formula, which already worked before. As i mentioned in the text ("In my case I would now like to calculate the logarithm.") i want to calculate the logarithm of the division. There is the Error.
Do you have a hint?

Regards,

[amitchandak · Accepted solution]

LN only working for non zero positive numbers. I checked and changed formula like this and it is working

DailyChange LN = if(Fact_Stocks[DailyChange]<>0, ln(abs(Fact_Stocks[DailyChange])))

Check file at same location