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