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)


       

      • Cmcmahan's avatar
        Cmcmahan
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

        Quick question. For your data, do you have multiple values per date per index, or just the one?  If so, you may be able to get rid of the MAX expression.

         

        StartValue = CALCULATE(MAX(FatIndexHistoricalData[Value]),
           CALCULATETABLE(FIRSTDATE(FatIndexHistoricalData[Date]),
           FILTER(ALL(FatIndexHistoricalData), FatIndexHistoricalData[IndexKey]=SELECTEDVALUE(FatIndexHistoricalData[IndexKey]) && YEAR(FatIndexHistoricalData[Date])=YEAR(SELECTEDVALUE(FatIndexHistoricalData[Date])))))

        So I think the reason your version isn't working is because STARTOFYEAR (and STARTOFMONTH, etc) have a super fun property I found out about here: https://dax.guide/startofyear/

         

        The dates argument can be any of the following:

        • A reference to a date/time column. Only in this case a context transition applies because the column reference is replaced by

         

        It turns out that they are actually calculate a table of values at the current context, but because you're applying the IndexKey=2 at the same time, it finds the start of year, but that no longer is linked to an IndexKey.  So I've gone and changed the first date calculation to filter on matching year and matching IndexKey so that you can get the StartValue of any index.  The expression is more complicated, but it gets the value you want.