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...
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
     

     

     

some_bih's avatar
some_bih
Icon for Community Champion rankCommunity 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