Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
varunv11
Frequent Visitor

Natural Logarithm for the cumulative total

Hello, 

 

I am trying to find Natural Logarithm for the cumulative total i have. I am find it hard to do this on power bi. In excel i use the below formula, screen shot attached for reference. 

 

Excel Formula =(LN(B3/B2))*100

 

Please help. 

 

Warm Regards 

Varun Raj

 

LN ScreenShot.JPG

1 ACCEPTED SOLUTION

Hi,

 

Assuming that the days in your visual are dragged from a Calendar Table, try this measure

 

=LN([Cumu PL]/CALCULATE([Cumu PL],PREVIOUSDAY(Calendar[Date])))*100

 

Does this work?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @varunv11,

 

In Power BI, there is also have a dax function LN() which you could have a reference.

 

In addition, you could have a reference of EARLIER function to help you calculate the Natural Logarithm for the cumulative total.

 

If you still need help, please share some data sample and your expected output.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Dear Cherry @v-piga-msft,

 

Thank you for the respose. 

 

Attaching a sample xlsx file via wetransfer link. I need to calculate Sharpe Ratio for which i will use LN function in excel. I need to build a BI which will support this in a dashboard. 

 

https://wetransfer.com/downloads/62c2c1bcf90b3631bf72aa1de51bf4f320180715123825/9abc6bcc34b635fe9ffb...

 

Kindly assist. 

 

Warm Regards 

Varun Raj

Hi,

 

Try this calculated column formula

 

=IFERROR(LN(Data[Cumu PL]/LOOKUPVALUE(Data[Cumu PL],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Date]<EARLIER(Data[Date])))))*100,BLANK())

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Dear @Ashish_Mathur

 

Thank you for response. 

 

All the fields are calcuated as measures, including cumulative PL. I am not able to use lookupvalue on a measure. 

 

I am trying to learn power bi, kindly assist. 

 

Warm Regards 

Varun Raj

Hi,

 

I do not understand your question.  You first give me columns in an Excel worksheet prompting me to believe that those columns are a part of your data itself.  Now you say that they are measures.  Show a sample source data and the result you want to see on that source data.  If somthing is a meaure, you should state that very clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

My objective is to find Natural Logarithm for the cumulative total. As per the attached file, Cumu PL is a measure coming from the actual PL with date and time. I cannot share the actual data as its confidential, thats the reason i created a sample data with my required output. 

 

Apologies that i missed to communicate that cumu pl is a measure and not a column in excel. As per below screenshot, column highlighted in green is a measure, columns highlighted in yellow is what i need to create and this query i raised on the forum is for column named "LN". 

 

Capture3.JPG

Hi,

 

Assuming that the days in your visual are dragged from a Calendar Table, try this measure

 

=LN([Cumu PL]/CALCULATE([Cumu PL],PREVIOUSDAY(Calendar[Date])))*100

 

Does this work?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Dear @Ashish_Mathur

 

It works. Thanks a lot. 

 

Warm Regards 

Varun Raj

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.