Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dynamic measure for Overall return

Hey guys 🙂   I am looking for a solution in Power BI / Power Pivot for a measure.    Based on a number of user selections ("Product", "Years remaining (from beginning)", "start date", "end date"...
v-xicai's avatar
v-xicai
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

You may create measures like DAX below.

 

Overall return =
VAR _SelectDate =
    SELECTEDVALUE ( Table1[Date] )
VAR _StartDate =
    CALCULATE ( MIN ( Table1[Date] ), ALLSELECTED ( Table1[Date] ) )
VAR _EndDate =
    CALCULATE ( MAX ( Table1[Date] ), ALLSELECTED ( Table1[Date] ) )
RETURN
    CALCULATE (
        SUM ( Table1[return] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Product], Table1[Years remaining] ),
            _SelectDate >= _StartDate
                && _SelectDate <= _EndDate
        )
    )

Best Regards,

Amy 

 

Community Support Team _ Amy

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Amy (v-xicai)

 

When trying to create your measure I receive the following error message: "Failed to resolve name 'SELECTEDVALUE'. It is not a valid table, variable, or function name." How should I continue from here?

 

Furthermore, I do not believe that this DAX will take the dynamic aspect of the "increasing" years remaining into account - how do you see this?

 

Best,

Anton

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi Anonymous ,

     

    I have created the following codes:

    • Date table (disconnected) for date slicers
    • Year table (disconnected) for year remaing slicer

    Added the measures below:

     

    Overall Return = 

PRODUCTX(FILTER(Sheet1, [Values_Selection] = 1), (1+(Sheet1[return])) ) -1

Number of Year = SELECTEDVALUE(Years[Years Remainng])

Values_Selection = 
IF (
    ROUNDDOWN (
        DATEDIFF ( SELECTEDVALUE ( Sheet1[Date] ), MAX ( 'DAte'[Date] ), DAY ) / 365,
        0
    ) + [Number of Year]
        = MAX ( Sheet1[Years remaining] )
        && SELECTEDVALUE ( Sheet1[Date] ) >= MIN ( 'DAte'[Date] )
        && SELECTEDVALUE ( Sheet1[Date] ) <= MAX ( 'DAte'[Date] )
        && MIN ( Sheet1[Years remaining] ) >= SELECTEDVALUE ( Years[Years Remainng] ),
    1,
    BLANK ()
)

     

    Check PBIX file an tell me if there is the need for any changes.