Get value of a year's first date available

[Anonymous]

Hi community,

I want to calculate YTD and QTD rate of returns of an index. The calculation should be as follows: (Current Price - Reference Price) / Reference Price

Reference Price should be the first price given for the current year. The problem is that this does not always equals the date 01/01/YYYY.

My table does not contain rows with dates when there was no price. The question is therefore how I can get the price value for the first date available. For instance, here this would be the price corresponding to 02.01.2020.

Same question if I want to search for the current quarter's first price available.

Thanks for any support.

 Regards.

[Ashish_Mathur]

Hi,

See if this article helps - LASTNONBLANK Explained - Excelerator BI.

[Nathaniel_C · Accepted solution]

Hi @Anonymous ,

Please try this measure:

Index Value =
VAR CurYear =
    YEAR ( MAX ( 'Table'[DATE] ) )
VAR CurValue =
    MAX ( 'Table'[VALUE] )
VAR EarliestDate =
    CALCULATE ( MIN ( 'Table'[DATE] ), YEAR ( 'Table'[DATE] ) = CurYear )
VAR _minValue =
    CALCULATE ( MIN ( 'Table'[VALUE] ), 'Table'[DATE] = EarliestDate )
VAR indexValue = CurValue - _minValue
RETURN
    indexValue

Please see my data table and result below.  This checks for the year, and subtracts the value from the first date visible for that year from the current value.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel