Forum Discussion

hcova7's avatar
hcova7
Frequent Visitor
1 year ago
Solved

Creating Index base 100 charts

Hi there. I have been trying to code a base 100 index DAX measure to chart some bourse indexes prices like DJI, NASDAQ, and so on, using daily data.   I would like to get this typical index value ...
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    Hi again hcova7 

    Thanks for testing that out, and for highlighting the issue with blanks 🙂

     

    I have attached an updated workbook.

     

    To fill the blanks, we can make these updates:

     

    Close Latest :=
VAR MaxDate = MAX ( 'Calendar'[Date] )
VAR MaxDateWithData =
    CALCULATE ( MAX ( Table1[DATE] ), 'Calendar'[Date] <= MaxDate )
VAR Result =
    CALCULATE ( [Close Average], 'Calendar'[Date] = MaxDateWithData )
RETURN
    Result
    IndexBase100 Fix :=
VAR MinDateFiltered =
    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
VAR MinDateInPeriod =
    CALCULATE ( MIN ( Table1[Date] ), ALLSELECTED ( 'Calendar' ) )
VAR MaxDateToStartOfPeriod =
    CALCULATE ( MAX ( Table1[Date] ), 'Calendar'[Date] <= MinDateFiltered )
VAR _BaseDate =
    COALESCE ( MaxDateToStartOfPeriod, MinDateInPeriod )
VAR _BasePrice =
    CALCULATE ( [Close Average], 'Calendar'[Date] = _BaseDate )
VAR _Index100 =
    DIVIDE ( [Close Latest], _BasePrice ) * 100
RETURN
    _Index100

     

    I did include some alternative formulations of the Index measure for comparison:

    • IndexBase100 Fix v2 (performs about the same)
    • IndexBase100 Fix v3 (quite a bit slower)

    Is this what you were looking for and is performance acceptable?

OwenAuger's avatar
OwenAuger
Super User
1 year ago

Hi hcova7 

Here is how I would write the measure.

1. First create Close Average which averages the CLOSE price over the filtered date range. The AVERAGE aggregation is not too important as this will normally be used by other measures at a daily level.

Close Average :=
AVERAGE ( Table1[CLOSE] )

2. Then create Close Latest, which returns the value of Close Average at the latest date within the visible values of 'Calendar'[Date]. This measure is useful when aggregating at month, quarter, year level, as it generally makes sense to show the latest index value.

Close Latest :=
LASTNONBLANKVALUE ( 'Calendar'[Date], [Close Average] )

3. Finally, create IndexBase100 Fix:

IndexBase100 Fix :=
VAR _BasePrice =
    CALCULATE (
        FIRSTNONBLANKVALUE ( 'Calendar'[Date], [Close Latest] ),
        ALLSELECTED ( 'Calendar' )
    )
VAR _Index100 =
    DIVIDE ( [Close Average], _BasePrice ) * 100
RETURN
    _Index100

I have attached an updated Excel workbook, and a sample chart looks like this:

There might be some adjustments needed to handle cases when indexes begin on different dates, but I hope this is a useful starting point.

 

Does this work for you?