Forum Discussion

faridelmjabber's avatar
faridelmjabber
Frequent Visitor
6 years ago

excel formula in dax

Hello, can someone help me on how can I get the result highlighted in red?     thanks
Need Help
v-eachen-msft's avatar
v-eachen-msft
Community Support
6 years ago

Hi faridelmjabber ,

 

Measure can be created.

Measure 2 =
VAR _first_value =
    CALCULATE (
        LOOKUPVALUE ( Sheet1[value], Sheet1[date], MIN ( Sheet1[date] ) ),
        ALL ( Sheet1 )
    )
VAR _last_value =
    CALCULATE (
        LOOKUPVALUE ( Sheet1[value], Sheet1[date], MAX ( Sheet1[date] ) ),
        ALL ( Sheet1 )
    )
VAR _first_gradi =
    CALCULATE (
        LOOKUPVALUE ( Sheet1[gradi], Sheet1[date], MIN ( Sheet1[date] ) ),
        ALL ( Sheet1 )
    )
VAR a =
    IF (
        SELECTEDVALUE ( Sheet1[value] ) = _first_value,
        SELECTEDVALUE ( Sheet1[value] ),
        (
            ( _last_value - _first_value )
                / ( SUMX ( ALL ( Sheet1 ), Sheet1[gradi] ) - _first_gradi )
        )
            * SELECTEDVALUE ( Sheet1[gradi] )
    )
RETURN
    a
Measure 5 =
SUMX (
    FILTER ( ALL ( Sheet1 ), Sheet1[date] <= MAX ( Sheet1[date] ) ),
    [Measure 2]
)

Measure 5 is the final result.
Here is my test file for your reference.

 

faridelmjabber's avatar
faridelmjabber
Frequent Visitor
6 years ago

v-eachen-msft 

thank you,
I still have a small problem, if the first value in the "value" column is null or is equal to 0 the furmola does not work,
how can i solve?

  • faridelmjabber's avatar
    faridelmjabber
    Frequent Visitor
    6 years ago

    another problem, if I change a value that becomes very different, the results are erroneous as shown: