Forum Discussion

Svsp's avatar
Svsp
Regular Visitor
3 years ago
Solved

Iteration Result Dax

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.

Need Help
Tips and Tricks
  • some_bih's avatar
    some_bih
    3 years ago

    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
     

     

     

1 Reply

  • some_bih's avatar
    some_bih
    Community Champion
    3 years ago

    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