Forum Discussion
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
- CmcmahanResident 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.- AnonymousNot 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.
- Ashish_MathurSuper User
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.
- AnonymousNot 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.pbixThank you for aswering.
- Ashish_MathurSuper User