Creating Index base 100 charts

[hcova7]

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 line chart.

My goal with this this DAX measure is to fill the column C shown in the below Excel sheet, starting in cell C6 with an Index value = 100

For this purpose I wrote the following DAX code shown below, but it doesn´t work.  I mean it doesn´t write any number on the sheet.
I guess my error is in how I am using the CALCULATE function.

In this code the index value must be = 100 at _StartDate. The close price at _StartDate is stored in _BasePrice.

Then index value for any other date is calculated as Close[Date] / _BasePrice x 100.

For instance, in the above table to december 10 (Nasdaq =1565.48) the index should be 1565.48 / 1681.64 x 100 = 93.09 (Here I am assuming that _StartDate is november/5/2008)

VAR _CurrentDate =
     MAX( 'Calendar'[Date] )

VAR _StartDate =
     CALCULATE(
     MIN( 'Calendar'[Date] ),
     ALLSELECTED( 'Calendar' )
)

VAR _EndDate =
     CALCULATE(
     MAX( 'Calendar'[Date] ),
     ALLSELECTED( 'Calendar' )
)

VAR _Range =
     DATESBETWEEN(
     'Calendar'[Date],
     _StartDate,
     _CurrentDate
)

VAR _BasePrice =
     CALCULATE(
     FIRSTNONBLANK(Table1[CLOSE], 0),
     FILTER(Table1, Table1[DATE] = _StartDate)
)

VAR _Index100 =
     CALCULATE(
     DIVIDE(
          SUMX(
                Table1,
                Table1[CLOSE]
           ),
          _BasePrice
       ) * 100
      ,
      _Range
)

RETURN

      _Index100

I have structurated the code using _StartDate, _EndDate and _Range to use the DAX code with timelines.

Any help is welcome

Here is the link with the excel spreadsheet.

https://docs.google.com/spreadsheets/d/1QTiThDnik2t3HRy3xfVsFpAxSo-qe8or/edit?usp=sharing&ouid=10086...

Thanks a lot

[hcova7]

Dear Owen.
Thanks a lot  for your time and your great answer. It works in 95% for my job.
However I have a problem when I try to include an index that publishes data values for some dates only and when I try to chart it with major indexes like DJI, NASDAQ, etc, that contain data values in all dates.
Let me explain this with a real case that  I have to deal with (I have changed the real fund name)

Royal Cloud is private fund that publish their data some dates during the month. No everyday.

When I try to compare it with the the major indexes here the problem:

As you can see in the above Excel table, Royal Cloud Index base 100  is in column D and calculated according your DAX measure. This measure leaves some cells in blank so the chart for this fund displays a scatter plot (see right chart).

However, if the measure could fill the gaps with the its last calculated value, the plot will be a continuos line (see below picture). I have manually done this. I have filled the gaps in column D writing in red the last calculated values from the measure.

Question:
How can I change the DAX measure so the blank cells shown in the Index base 100 table can be filled with the last value calculated by the measure?

Best regards

Here the link for the worksheet with the Royal Cloud Fund data:

https://docs.google.com/spreadsheets/d/1GwqZIHr8xjq0LZPXzww8dZD_yvcPi3Ay/edit?usp=sharing&ouid=10086...

[hcova7]

Dear Owen:
Thanks you very much for your new measure.
It works fine.
Thanks a lot again for you time.

Best regards and have a nice weekend

[OwenAuger · Accepted solution]

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]

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?