Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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?
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
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.
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())
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.
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".
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?
You are welcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |