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?

hcova7's avatar
hcova7
Frequent Visitor
1 year ago

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=100866832098495107517&rtpof=true&sd=true

 







  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    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?

  • hcova7's avatar
    hcova7
    Frequent Visitor
    1 year ago

    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