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") - see the table in the top of the screenshot from the sample spreadsheet below (I will gladly send the sample workbook over if needed) - I would like to calculate the overall return (marked with yellow) in Power BI.

 

To illustrate what is happening with the data, I have added the hardcoded table in the middle of the spreadsheet. This is just to highlight what is happening with the main data (located in the table farthest to the left). Please pay attention to the fact that the measure should be able to handle that "Years remaining" must increase everytime there is one year difference between the selected "End date" and the date of return (making it dynamic). Before the first one year diff. the "years remaining" should be equal to the selection in the user selection.

 

So to sum it all up:

I hope you can help me with a measure which based on the user selection and the dynamic yearly increasing "years remaining" based on "End date" can calculate the overall return directly from our Data.

 

I look forward to see what you got!

 

13 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi Anonymous ,

     

    Can you share the sample file please? If possible can you tell me how the overall calculation is made? if the excel file has the calculation then I can check the formula there.

     

    It's a lot of data to punch in a power bi file. thank you.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi MFelix 

       

      Thanks for taking this up!

       

      I will for sure share the sample file, but I do not seem to be able to do it here through the community.

       

      Do you have an email which I can send it too instead?

       

      Thanks 🙂

      • MFelix's avatar
        MFelix
        Super User
        6 years ago

        Hi Anonymous ,

         

        Here in the community you need to send the files trough onedrive, googledrive, we transfer or similar type of links.

         

        Don't forget if you are doing it trough Goggle or Onedrive don't put a password on it. If you prefer you can also send the link in private message using the same type of links.

  • v-xicai's avatar
    v-xicai
    Community 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
        Super 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.