Iteration Result Dax

[Svsp]

Hi, 

I am trying to convert Excel sheet formulas to Power BI Dax. Right now I am facing an issue in converting one of the formula.

I have 3 columns date,value,result.

I am trying to write Dax for result column.

Result Excel formula is for every 1st day of the month should have same value and rest should use below calculation

(1+today value)*(1+previous day result)-1

Example: 

1/1/2023:-0.20

1/2/2023:- (1+0.10)*(1+0.20)-1

1/3/2023:- (1+0.22)*(1+0.24)-1

Here is the sample data:

Date            value       result

1/1/2023       0.20         0.20

1/2/2023.      0.10         0.24  

1/3/2023.      0.22         0.27

1/4/2023.      0.08         0.37

Could you please help me in achieving this calculation in power bi.

[some_bih · Accepted solution]

Hi @Svsp create two calculated columns as shown below. 
Note for 2.1.2023 amount is 1,1*1,2-1=0,32 not 0,24

 Did I answer your question? Mark my post as a solution! Kudos Appreciated!

 

Previous_day_value =

--adjust Sheet1 to Table name

VAR _currentRowDate = Sheet1[Date]

VAR _previousRowDate =

    CALCULATE (

        MAX ( Sheet1[Date] ),

        FILTER ( ALL ( Sheet1 ), Sheet1[Date] < _currentRowDate)

    )

VAR _result=

    IF (

        NOT ISBLANK ( _previousRowDate ),

        CALCULATE (

            MAX ( Sheet1[Value] ),

            FILTER ( ALL ( Sheet1 ), Sheet1[Date] = _previousRowDate )

        )

    )

RETURN _result

 ---

Result Test =

VAR _today_value=Sheet1[Value]

VAR _previous_day_value=Sheet1[Previous_day_value]

VAR _result=(1+_today_value)*(1+_previous_day_value)-1

RETURN _result