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 (FatIndexHistoricalData[IndexKey]=2) and returning the value of index 1 (DateKey 20190102). Can someone help me please?

 

Table:

Column:

 

Date = DATE(LEFT(FatIndexHistoricalData[DateKey],4),MID(FatIndexHistoricalData[DateKey],5,2),RIGHT(FatIndexHistoricalData[DateKey],2))

Measure:

 

YTD Profit Index 2 = 

    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(FatIndexHistoricalData,FatIndexHistoricalData[Date]=STARTOFYEAR(FatIndexHistoricalData[Date])&&FatIndexHistoricalData[IndexKey]=2) // 2 - IBOVESPA
        )

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

thank you,

Portes, Leo

 

 

 

 

8 Replies

  • Cmcmahan's avatar
    Cmcmahan
    Resident Rockstar

    Ah, this is tricky but I figured it out.

     

    You don't have a value for Jan 1, 2019 for index =2.  STARTOFYEAR gets a date, and returns Jan 1 with the same year.

     

    You're trying to check what the [Value] is when [Date]=1/1/2019, which is null.

    Instead of using STARTOFYEAR, use FIRSTDATE and another filter constraint to make sure the year is the same.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Cmcmahan wrote:

      Ah, this is tricky but I figured it out.

       

      You don't have a value for Jan 1, 2019 for index =2.  STARTOFYEAR gets a date, and returns Jan 1 with the same year.

       

      You're trying to check what the [Value] is when [Date]=1/1/2019, which is null.

      Instead of using STARTOFYEAR, use FIRSTDATE and another filter constraint to make sure the year is the same.


      Cmcmahan ,
      Well noted, I thought the function returned the first value of the year and not the first date. I'll try to resolve using FIRSTDATE.

      Thank you for aswering.

  • 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

      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.