Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

get value by Category using STARTOFYEAR

Dear, I'm trying to calculate the annual profitability of IndexKey 2, using the most recent value and the first value of the year. But the variable StartValue is ignoring the filter (FatIndexHistori...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

So for IndexKey 2, you are expecting the answers to be 92031.86 and 91840.8.  Am i correct?  Share the link from where i can downlod your PBI file.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    Ashish_Mathur wrote:

    Hi,

    So for IndexKey 2, you are expecting the answers to be 92031.86 and 91840.8.  Am i correct?  Share the link from where i can downlod your PBI file.

     

    Ashish_Mathur ,
    You are right, see the link below:
    Index.pbix

     

    Thank you for aswering.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      Hi,

      You may download my PBI file from here.

      Hope this helps.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Ashish_Mathur ,
        Thank you for your help! I've tried a similar solution using FIRSTNONBLANK and LASTNONBLANK, but didn't work because I'm having to calculate the cumulative return always referencing the first value of the year (in this case 91840.8).

        I set the value in the variable in the example below to show what I wish to do.

        YTD Profit iBovespa = 

    VAR LastValue = 
        CALCULATE(
            MAX(FatIndexHistoricalData[Value]),
            FILTER(FatIndexHistoricalData,FatIndexHistoricalData[Date]=MAX(FatIndexHistoricalData[Date])&&FatIndexHistoricalData[IndexKey]=2) 
        )

    VAR StartValue = 91840.8
//        CALCULATE(
//           MAX(FatIndexHistoricalData[Value]),
//           FILTER(ALL(FatIndexHistoricalData),FatIndexHistoricalData[Date]=STARTOFYEAR(FatIndexHistoricalData[Date])&&FatIndexHistoricalData[IndexKey]=2)
//        )

RETURN (((LastValue / StartValue) - 1) * 100)