Formula that includes own column in DAX

[HumanX]

Hi! 

Is there a possible way that my Result_column is included at my formula in DAX?

Example: I have column: Accuracy, Form and Result_column

Result_column =  (Accuracy * Form) / previous data of Result_Column

Note: if possible that for every upload, first data of Form column will be the same as Result_Column. So if the first value of Form is 5 then 5 will be used on the formula and continue as there will be more data as output.

hope anyone can help thanks!

[123abc]

Here's an example formula you can use for the Result_column:

Result_column =
IF(
      ISBLANK(PREVIOUSMONTH('YourTable'[DateColumn])),
       'YourTable'[Form],
      DIVIDE(
                'YourTable'[Accuracy] * 'YourTable'[Form],
      CALCULATE(
             MAX('YourTable'[Result_column]),
            PREVIOUSMONTH('YourTable'[DateColumn])
             )
          )
        )

Please replace 'YourTable' with the actual name of your table and 'DateColumn' with the column that represents the date or the order of the rows.

This formula uses the PREVIOUSMONTH function to refer to the previous row in terms of the date. If your data has a different chronological order, you might need to use a different function based on your specific requirements.

The formula checks if the current row is the first row (where there is no previous month), and if so, it sets the Result_column to the value of the Form column. Otherwise, it calculates the Result_column based on the formula you provided, using the previous row's Result_column.

[HumanX]

Hi @123abc ,

Your formula looks good and i was able to create an idea about that but whenever I used the Result_column inside the formula for my Result_column it has error like I cannot use my Result_column in my formula inside Result_column. 


I just want to use my Result_column's previous data to be part of the formula which is the divider. It's kinda tricky because the history output of Result_column will be use as divider. Any suggestions will be a great help. Thank you!

[123abc]

I see, if you're encountering an issue using Result_column within its own calculation, you can try using a different approach. You can create a separate calculated column that represents the lagged value of Result_column and then refer to this column in your main calculation. Here's an example:

Result_column =
VAR InitialValue = FIRSTNONBLANK('YourTableName'[Form], 'YourTableName'[Form])
VAR LaggedResult = 'YourTableName'[Lagged_Result_column]
RETURN
IF(
ISBLANK(LaggedResult),
InitialValue,
('YourTableName'[Accuracy] * 'YourTableName'[Form]) / LaggedResult
)

Lagged_Result_column =
CALCULATE(
MAX('YourTableName'[Result_column]),
FILTER(
ALL('YourTableName'),
'YourTableName'[Date] < EARLIER('YourTableName'[Date])
)
)

In this example, I've added a new column called Lagged_Result_column that calculates the maximum value of Result_column for dates prior to the current row. The Result_column then references this lagged value.

Replace 'YourTableName', 'Date', and other column names with your actual table and column names.

This approach helps avoid the issue of referencing a column within its own calculation directly. It calculates the lagged value separately and then uses it in the main formula.

[HumanX]

Thank you! I'll update if this will works. As of now I can't fix this as the error says Circular Dependencies because of reference.

[123abc]

Circular dependencies can occur when a calculated column refers to itself directly or indirectly, leading to a loop. In such cases, you may need to consider a two-step approach where you first create a column that calculates the intermediary values, and then use that column to calculate the final result. Here's an example:

Create an Intermediary Column:

Intermediary_Column =
VAR FirstFormValue = FIRSTNONBLANK('YourTable'[Form], 1)
RETURN
IF(
ISBLANK(PREVIOUSMONTH('YourTable'[Result_column])),
FirstFormValue,
('YourTable'[Accuracy] * 'YourTable'[Form]) /
CALCULATE(
FIRSTNONBLANK('YourTable'[Result_column], 1),
FILTER(
ALL('YourTable'),
'YourTable'[Date] = EARLIER('YourTable'[Date]) - 1
)
)
)

Use the Intermediary Column for the Result Column:

Result_column = 'YourTable'[Intermediary_Column]

This way, you break the circular dependency by first calculating an intermediary column and then using that column to calculate the final result. Ensure that the intermediary column is updated before the final result column in your data model.

Remember to replace 'YourTable' with the actual name of your table, and adjust column names accordingly if they are different in your dataset.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[HumanX]

Hi! @123abc 

Sorry for more questions but i just used above as sample on how to get the previous data.

Here's a formula used for computing the result from excel and want to migrate this to power BI.

Form = average of the data (per column)
Cumulative Form  = typical formula for cumulative of Form 
Accuracy = another data

Result = Getting hard time to figure

To break the formula for Result
(([previous Data of  Result] * [previous Data of Cumulative Form]) + ([current data of Accuracy] * [Current Data of Form]))/Current data of Cumulative Form

C234 or Result=((.67*5)+(69% *6))/11
Result= .679733 or 68%

Note: the first value for result also the first value for cumulative Form so the formula will be applied on the next. This is somehow like circular. Any suggestions will be much appreciated.

Thank you! 

[123abc]

o import an Excel workbook with formulas to Power BI Desktop, you can follow these steps:

1. Import the Excel workbook into Power BI Desktop by selecting File > Import > Power Query, Power Pivot, Power View.
2. From the Open window, select an Excel workbook to import.
3. From the import dialog box that appears, select Start.
4. Select Close.
5. In Power BI Desktop, go to the ribbon, and select Data source settings.
6. In the pop-up window, select the data source (i.e. the Excel file) that needs to be redirected and click Change Source.
7. In the Excel pop-up window, click Browse and navigate to the new file location.
8. Click OK and your file will be connected again to your Power BI report 1.

Regarding the formula for Result, you mentioned that the first value for Result is also the first value for Cumulative Form. This means that the formula will be applied to the next value of Result. This is a circular reference, which is not supported in Power BI. However, you can use the DAX function EARLIER() to reference the previous row in a table. Here’s an example of how you can modify your formula to use EARLIER():

Result = 
VAR PreviousResult = EARLIER(Result)
VAR PreviousCumulativeForm = EARLIER(Cumulative Form)
VAR CurrentAccuracy = [Accuracy]
VAR CurrentForm = [Form]
VAR CurrentCumulativeForm = [Cumulative Form]

RETURN
(([PreviousResult] * [PreviousCumulativeForm]) + ([CurrentAccuracy] * [CurrentForm])) / [CurrentCumulativeForm]

This formula uses EARLIER() to reference the previous row’s Result and Cumulative Form values. The VAR statements define variables to store the previous and current values of Result, Cumulative Form, Accuracy, and Form. The RETURN statement calculates the new value of Result using the variables 1.

I hope this helps! Let me know if you have any other questions.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.