I have to get previous value from the same calculated column that was being constructed dynamically

[Anonymous]

Hi,

I am stuck at creating a EC(Equity Curve values) caculated column using Dax language. I have elobarated the table as shown below in power BI desktop by adding custom column functions in power Query. Now I have to create calculated column in DAX that will give values as per the formula shown in excel file by using MTM column shown in power BI desktop figure 1. by taking initial value of EC column as 100.

[Anonymous]

Thankyou so much,I have implemented this logic and can able to find get EC values without taking initial value as 100 at first. But I have wrote one more measure to add 100(to start initial EC value as 100) and multiply the result with 100 to get final and correct(changes this way give same result for EC) Values. Now, I could solve my issue with your support. I really appreciate your work.

[Anonymous]

Hi @Anonymous ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[123abc]

Creating an Equity Curve (EC) in Power BI using DAX based on the Mark-to-Market (MTM) values can be challenging, especially if you want to perform a calculation based on the previous row's value dynamically.

Here's how you can create a DAX calculated column for the Equity Curve:

1. Initialize EC Column: You mentioned that you want the initial value of the EC column to be 100. You can set this as the default value for the first row.

2. Calculate EC: For subsequent rows, you can use the formula to calculate the EC based on the previous row's EC value and the current MTM value.

   Here's a basic example of how you can achieve this:

   EC Calculated Column =
   VAR CurrentRowMTM = TableName[MTM]
   VAR PreviousEC =
   IF(
   ROWNUMBER() = 1, -- Check if it's the first row
   100, -- Initial value of EC
   CALCULATE(
   MAX(TableName[EC]),
   FILTER(ALL(TableName), TableName[Index] = EARLIER(TableName[Index]) - 1)
   )
   )
   RETURN
   PreviousEC + CurrentRowMTM

   Creating an Equity Curve (EC) in Power BI using DAX based on the Mark-to-Market (MTM) values can be challenging, especially if you want to perform a calculation based on the previous row's value dynamically.

   Here's how you can create a DAX calculated column for the Equity Curve:

   1. Initialize EC Column: You mentioned that you want the initial value of the EC column to be 100. You can set this as the default value for the first row.

   2. Calculate EC: For subsequent rows, you can use the formula to calculate the EC based on the previous row's EC value and the current MTM value.

      Here's a basic example of how you can achieve this:

      DAXCopy code

      EC Calculated Column = VAR CurrentRowMTM = TableName[MTM] VAR PreviousEC = IF( ROWNUMBER() = 1, -- Check if it's the first row 100, -- Initial value of EC CALCULATE( MAX(TableName[EC]), FILTER(ALL(TableName), TableName[Index] = EARLIER(TableName[Index]) - 1) ) ) RETURN PreviousEC + CurrentRowMTM

      Note:

      • Replace TableName with the actual name of your table.
      • Ensure you have a unique index or a date column that defines the order of rows in your table. I used Index as an example; you should replace it with the appropriate column that defines the order.
      • The EARLIER() function allows you to reference the current row context within a calculated column in DAX.
        1. Steps:
           • Add this DAX formula to create a new calculated column in your table in Power BI.
           • Ensure you have sorted your table appropriately (usually by date or index).
           • Refresh your Power BI model.

             By following the steps and using the formula provided, you should be able to create an Equity Curve calculated column based on the Mark-to-Market values, with an initial value of 100 for the first row.

[Anonymous]

This didn't work. Is there any other solution to this?

[Anonymous]

Where do I get EC column in Max Function that itself have to be calculated, right? ..which you have return in Dax code.For the time being I have taken EC column with 100 as values in all the rows and copied your code. Also It is stating an error that ROWNUMBER() parameter must be table.  I am really thankful that you tried solving my problem. Awaiting for your response.

[123abc · Accepted solution]

I understand your concern. Let's break down the problem and try a different approach.

The primary challenge here is that you want to create a dynamic Equity Curve (EC) based on a Mark-to-Market (MTM) column in a calculated column. In DAX, calculated columns are computed during the data refresh and cannot refer to the same calculated column in their formula, which makes it challenging.

However, you can achieve this using a measure rather than a calculated column. Measures in DAX are computed at query time, and you can refer to previous values using patterns like cumulative totals.

Here's how you can create a measure for the Equity Curve:

1. Create a New Measure: Go to your Power BI Desktop and create a new measure for the Equity Curve. This measure will use a pattern known as a cumulative total.

EC Measure =
VAR CurrentDate = MAX('YourTableName'[Date]) // Replace 'YourTableName' and '[Date]' with your actual table and date column names
RETURN
CALCULATE(
SUM('YourTableName'[MTM]),
FILTER(
ALLSELECTED('YourTableName'),
'YourTableName'[Date] <= CurrentDate
)
) + 100 // Starting with an initial value of 100

1. How it Works:

   • CurrentDate: Gets the current date context in the visualization or filter.
   • CALCULATE(SUM('YourTableName'[MTM]), ...): Calculates the sum of MTM values up to the current date.
   • FILTER(ALLSELECTED('YourTableName'), ...): Filters the table to include only rows up to the current date.
   • The + 100 at the end ensures you start with an initial value of 100.

2. Use the Measure in Visualizations: Once you've created the measure, you can use it in your Power BI visuals to display the dynamic Equity Curve based on the MTM values.

This approach should give you a dynamic Equity Curve based on the MTM values without relying on a calculated column that refers to itself. Remember to adjust table and column names in the DAX measure according to your actual data model in Power BI.

I hope this approach helps you achieve the desired outcome. Let me know if you have further questions or need additional assistance!